Formula Help with Count Number of Vessel Turnaround Trips monthly based on Criteria

Lukma

Board Regular
Joined
Feb 12, 2020
Messages
249
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Hi Friends

Good day to you all and I hope everyone is having a great weekend, Please i would appreciate it if anyone could help out with a formula that will count the vessel turnaround trip weekly basis per each vessel and the average for the full month since my boss came up with something new...

my data Entry is as seen, in this case, I will explain and give an example of what I meant and I would like to have the result to be.
Example of Vessel Turnaround. So let us use Column D I have the is (MUS No: 3535), which is the first voyage for ADNOC-230 In Column E, let's say now in Column O is the departure date & time 01/12/2023 at 18:30:00
to complete this voyage she needs to return to the Port to complete that MUS Voyage and her return date is 03/12/2023 at 22:30:00 this will be counted as one Trip and when she goes again and back again this will be counted a two Trip so I need help with a formula to get this counted for each trip weekly in Column V-4, W, X, Y, Z based on criteria vessel name.

So I have the list of Vessel Name Fix in Column T 4, and I have my Month validated in Column U 2
In Column C, I have my Week Number

Also in Column P, I need to have the return date and in Column -Q, will be the total days she spent outside before returning to Port. I also placed an Example of what i need all formula to work out for me.

Appreciate any help and support from all my friends

Regards


2023 - VESSELS ONSHORE JETTY OPERATIONS.xlsx
BCDEFGHIJKLMNOPQRSTUVWXYZAA
2Month Filter >>>December 2021
3Month WK NUMMus NoSupply VesselVessel Arrived FWB Date&TimeManifest Date & TimeControl Tower Permission Date & TimeBerth Position Date & TimeBerth NosVessel Arrive Secured Berth Date & TimeBerth NosStart Cargo Deck offload&LoadComplete Date & Time Departure Adnoc Port Date & Time Vessel Return Date & TimeTotal Spent DaysVessels NameTotal Nos Of TripsWk-1Wk-2Wk-3Wk-4Wk-5Average Trip
4Dec-2313532MUTAWA-30429/11/23 01:0030/11/23 10:5030/11/23 19:0001/12/23 00:3001/12/23 00:30B10-101/12/23 00:5501/12/23 04:3001/12/23 13:15ADNOC-1010
5Nov-2353533LCT-TARFFAH-129/11/23 10:3030/11/23 11:3530/11/23 11:4530/11/23 15:45B3-230/11/23 19:20B3-130/11/23 20:0001/12/23 00:1001/12/23 01:45ADNOC-1011
6Dec-2313534SWISSCO RUBY29/11/23 01:5030/11/23 13:2029/11/23 22:2001/12/23 02:0001/12/23 02:00B6-101/12/23 02:5501/12/23 16:3001/12/23 17:30ADNOC-221
7Dec-2313535ADNOC-23030/11/23 14:3030/11/23 16:3330/11/23 19:2501/12/23 00:2001/12/23 00:20B1-101/12/23 00:5501/12/23 17:0001/12/23 18:3003/12/23 22:302.2ADNOC-222
8Dec-2313536WARDEH-130/11/23 19:2030/11/23 19:1501/12/23 03:4001/12/23 07:1001/12/23 07:10B11-101/12/23 08:3001/12/23 21:1001/12/23 22:00ADNOC-223
9Nov-2353537LCT RAZAN-129/11/23 01:5030/11/23 20:1929/11/23 02:2030/11/23 22:4530/11/23 22:45B8-130/11/23 23:4001/12/23 12:4501/12/23 13:10ADNOC-224
10Dec-2313538STANFORD ALPHA29/11/23 05:0030/11/23 22:4401/12/23 01:5501/12/23 03:1501/12/23 03:15B9-101/12/23 04:4801/12/23 22:4001/12/23 23:05ADNOC-225
11Dec-2313539JOPETWIL-6230/12/23 20:0030/11/23 22:3701/12/23 01:5201/12/23 06:3001/12/23 06:30B3-101/12/23 06:3001/12/23 15:5501/12/23 17:00ADNOC-226
12Dec-2313540CECILIE-K01/12/23 12:2401/12/23 15:5501/12/23 16:2501/12/23 23:1201/12/23 23:12B14-101/12/23 23:5402/12/23 12:2402/12/23 12:48ADNOC-227
13Dec-2313541AD-ASTRA01/12/23 18:0001/12/23 16:3001/12/23 16:5201/12/23 23:2501/12/23 23:25RORO01/12/23 23:3501/12/23 23:4701/12/23 23:59ADNOC-228
14Dec-2313542LCT BIMA30/11/23 21:2001/12/23 17:5001/12/23 18:0001/12/23 19:3001/12/23 19:3001/12/23 20:4502/12/23 09:3002/12/23 10:00ADNOC-229
15Dec-2313543ADNOC-22402/12/23 04:3001/12/23 19:4502/12/23 04:3002/12/23 08:3502/12/23 08:35B3-102/12/23 09:0002/12/23 16:1002/12/23 16:40ADNOC-230
16Dec-2313544LCT BUSHRA02/12/23 12:3701/12/23 21:3001/12/23 22:3802/12/23 17:3602/12/23 17:36B10-102/12/23 18:5003/12/23 00:3503/12/23 00:55ADNOC-510
17Dec-2313545VENTURE02/12/23 00:1501/12/23 22:3501/12/23 22:3802/12/23 04:2502/12/23 04:25B6-102/12/23 04:4002/12/23 08:5502/12/23 09:30ADNOC-511
18Dec-2313546A-CHLOE02/12/23 02:3002/12/23 00:4002/12/23 01:0002/12/23 06:1502/12/23 06:15B9-102/12/23 07:0002/12/23 23:5503/12/23 00:45ADNOC-512
19Dec-2313547ADNOC-101002/12/23 07:0002/12/23 02:0002/12/23 04:3002/12/23 11:1502/12/23 11:15B8-102/12/23 11:3502/12/23 20:4502/12/23 21:35ADNOC-810
20Dec-2313548ADNOC-22102/12/23 15:3002/12/23 11:1902/12/23 11:2502/12/23 20:0502/12/23 20:05B5-102/12/23 20:3003/12/23 08:2003/12/23 09:00ADNOC-811
21Dec-2313549LCT MARWAH-102/12/23 14:0002/12/23 13:3002/12/23 13:3302/12/23 18:1502/12/23 18:15B4-102/12/23 19:1503/12/23 01:0503/12/23 02:45ADNOC-812
22Dec-2313550ADNOC-22602/12/23 16:5502/12/23 17:3002/12/23 05:4002/12/23 21:1502/12/23 21:15B1-102/12/23 21:4503/12/23 20:3003/12/23 21:40ADNOC-850
23Dec-2313551ADNOC-22902/12/23 20:5002/12/23 18:3002/12/23 18:3703/12/23 01:0003/12/23 01:00B6-103/12/23 02:5504/12/23 13:3004/12/23 18:25ADNOC-851
24Dec-2313552JOPETWIL-6802/12/23 10:3002/12/23 17:2202/12/23 12:0002/12/23 22:5502/12/23 22:55B12-102/12/23 22:5503/12/23 22:0003/12/23 22:00ADNOC-A02
25Dec-2313553A-GRACE02/12/23 18:0002/12/23 20:0002/12/23 20:4003/12/23 01:2003/12/23 01:20B13-103/12/23 02:2503/12/23 12:2503/12/23 12:45ADNOC-A03
26Dec-2313554ADNOC-A0502/12/23 19:5002/12/23 21:2002/12/23 21:5103/12/23 01:4503/12/23 01:45B14-103/12/23 02:2503/12/23 20:3003/12/23 21:25ADNOC-A04
27Dec-2313555LCT-TARFFAH-103/12/23 10:0002/12/23 21:5003/12/23 06:5503/12/23 23:1503/12/23 23:15B3-103/12/23 23:4504/12/23 13:4004/12/23 14:15ADNOC-A05
28Dec-2313556M-SUPPORTER03/12/23 02:1003/12/23 00:5003/12/23 01:5003/12/23 06:3503/12/23 06:3503/12/23 12:5503/12/23 20:3003/12/23 20:50ADNOC-S01
29Dec-2313557LCT-JEWAHER-103/12/23 00:1003/12/23 02:0003/12/23 02:1003/12/23 05:5503/12/23 05:55B7-103/12/23 07:3504/12/23 11:0004/12/23 11:40ADNOC-S02
30Dec-2313558ADNOC-51103/12/23 00:3003/12/23 08:4803/12/23 08:5103/12/23 18:1003/12/23 18:10B5-103/12/23 19:3003/12/23 21:1503/12/23 22:00A-CHLOE
31Dec-2313559CREST RADIANT-503/12/23 05:0003/12/23 09:5503/12/23 10:0203/12/23 15:3003/12/23 15:30B2-103/12/23 16:0503/12/23 20:5804/12/23 08:40A-GRACE
32Dec-2313560WARDEH-103/12/23 11:0003/12/23 11:1503/12/23 22:3503/12/23 22:35B12-103/12/23 23:2004/12/23 11:2004/12/23 12:55A-LIBERTY
33Dec-2313561Z-OCEAN03/12/23 18:0003/12/23 13:1703/12/23 13:1603/12/23 21:0003/12/23 21:00B13-103/12/23 22:1804/12/23 01:4504/12/23 02:07AMS-ONYX
34Dec-2313562ADNOC-23003/12/23 15:0003/12/23 17:5003/12/23 18:3003/12/23 22:3003/12/23 22:30B1-103/12/23 23:0504/12/23 23:4005/12/23 00:15AMS-RUBY
2023-Vessel Onshore Operations
Cell Formulas
RangeFormula
B4:B34B4=IFERROR(EOMONTH(K4,-1)+1,"")
C4:C34C4=IFERROR(MATCH(DAY(K4),{1,8,15,22,29}),"")
Q7Q7=P7-O7
Cells with Data Validation
CellAllowCriteria
U2List=Info_Settings!$B$3:$B$99
X2List=Info_Settings!$B$3:$B$16
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Hi Friends

Good day to you all and I hope everyone is having a great weekend, Please i would appreciate it if anyone could help out with a formula that will count the vessel turnaround trip weekly basis per each vessel and the average for the full month since my boss came up with something new...

my data Entry is as seen, in this case, I will explain and give an example of what I meant and I would like to have the result to be.
Example of Vessel Turnaround. So let us use Column D I have the is (MUS No: 3535), which is the first voyage for ADNOC-230 In Column E, let's say now in Column O is the departure date & time 01/12/2023 at 18:30:00
to complete this voyage she needs to return to the Port to complete that MUS Voyage and her return date is 03/12/2023 at 22:30:00 this will be counted as one Trip and when she goes again and back again this will be counted a two Trip so I need help with a formula to get this counted for each trip weekly in Column V-4, W, X, Y, Z based on criteria vessel name.

So I have the list of Vessel Name Fix in Column T 4, and I have my Month validated in Column U 2
In Column C, I have my Week Number

Also in Column P, I need to have the return date and in Column -Q, will be the total days she spent outside before returning to Port. I also placed an Example of what i need all formula to work out for me.

Appreciate any help and support from all my friends

Regards


2023 - VESSELS ONSHORE JETTY OPERATIONS.xlsx
BCDEFGHIJKLMNOPQRSTUVWXYZAA
2Month Filter >>>December 2021
3Month WK NUMMus NoSupply VesselVessel Arrived FWB Date&TimeManifest Date & TimeControl Tower Permission Date & TimeBerth Position Date & TimeBerth NosVessel Arrive Secured Berth Date & TimeBerth NosStart Cargo Deck offload&LoadComplete Date & Time Departure Adnoc Port Date & Time Vessel Return Date & TimeTotal Spent DaysVessels NameTotal Nos Of TripsWk-1Wk-2Wk-3Wk-4Wk-5Average Trip
4Dec-2313532MUTAWA-30429/11/23 01:0030/11/23 10:5030/11/23 19:0001/12/23 00:3001/12/23 00:30B10-101/12/23 00:5501/12/23 04:3001/12/23 13:15ADNOC-1010
5Nov-2353533LCT-TARFFAH-129/11/23 10:3030/11/23 11:3530/11/23 11:4530/11/23 15:45B3-230/11/23 19:20B3-130/11/23 20:0001/12/23 00:1001/12/23 01:45ADNOC-1011
6Dec-2313534SWISSCO RUBY29/11/23 01:5030/11/23 13:2029/11/23 22:2001/12/23 02:0001/12/23 02:00B6-101/12/23 02:5501/12/23 16:3001/12/23 17:30ADNOC-221
7Dec-2313535ADNOC-23030/11/23 14:3030/11/23 16:3330/11/23 19:2501/12/23 00:2001/12/23 00:20B1-101/12/23 00:5501/12/23 17:0001/12/23 18:3003/12/23 22:302.2ADNOC-222
8Dec-2313536WARDEH-130/11/23 19:2030/11/23 19:1501/12/23 03:4001/12/23 07:1001/12/23 07:10B11-101/12/23 08:3001/12/23 21:1001/12/23 22:00ADNOC-223
9Nov-2353537LCT RAZAN-129/11/23 01:5030/11/23 20:1929/11/23 02:2030/11/23 22:4530/11/23 22:45B8-130/11/23 23:4001/12/23 12:4501/12/23 13:10ADNOC-224
10Dec-2313538STANFORD ALPHA29/11/23 05:0030/11/23 22:4401/12/23 01:5501/12/23 03:1501/12/23 03:15B9-101/12/23 04:4801/12/23 22:4001/12/23 23:05ADNOC-225
11Dec-2313539JOPETWIL-6230/12/23 20:0030/11/23 22:3701/12/23 01:5201/12/23 06:3001/12/23 06:30B3-101/12/23 06:3001/12/23 15:5501/12/23 17:00ADNOC-226
12Dec-2313540CECILIE-K01/12/23 12:2401/12/23 15:5501/12/23 16:2501/12/23 23:1201/12/23 23:12B14-101/12/23 23:5402/12/23 12:2402/12/23 12:48ADNOC-227
13Dec-2313541AD-ASTRA01/12/23 18:0001/12/23 16:3001/12/23 16:5201/12/23 23:2501/12/23 23:25RORO01/12/23 23:3501/12/23 23:4701/12/23 23:59ADNOC-228
14Dec-2313542LCT BIMA30/11/23 21:2001/12/23 17:5001/12/23 18:0001/12/23 19:3001/12/23 19:3001/12/23 20:4502/12/23 09:3002/12/23 10:00ADNOC-229
15Dec-2313543ADNOC-22402/12/23 04:3001/12/23 19:4502/12/23 04:3002/12/23 08:3502/12/23 08:35B3-102/12/23 09:0002/12/23 16:1002/12/23 16:40ADNOC-230
16Dec-2313544LCT BUSHRA02/12/23 12:3701/12/23 21:3001/12/23 22:3802/12/23 17:3602/12/23 17:36B10-102/12/23 18:5003/12/23 00:3503/12/23 00:55ADNOC-510
17Dec-2313545VENTURE02/12/23 00:1501/12/23 22:3501/12/23 22:3802/12/23 04:2502/12/23 04:25B6-102/12/23 04:4002/12/23 08:5502/12/23 09:30ADNOC-511
18Dec-2313546A-CHLOE02/12/23 02:3002/12/23 00:4002/12/23 01:0002/12/23 06:1502/12/23 06:15B9-102/12/23 07:0002/12/23 23:5503/12/23 00:45ADNOC-512
19Dec-2313547ADNOC-101002/12/23 07:0002/12/23 02:0002/12/23 04:3002/12/23 11:1502/12/23 11:15B8-102/12/23 11:3502/12/23 20:4502/12/23 21:35ADNOC-810
20Dec-2313548ADNOC-22102/12/23 15:3002/12/23 11:1902/12/23 11:2502/12/23 20:0502/12/23 20:05B5-102/12/23 20:3003/12/23 08:2003/12/23 09:00ADNOC-811
21Dec-2313549LCT MARWAH-102/12/23 14:0002/12/23 13:3002/12/23 13:3302/12/23 18:1502/12/23 18:15B4-102/12/23 19:1503/12/23 01:0503/12/23 02:45ADNOC-812
22Dec-2313550ADNOC-22602/12/23 16:5502/12/23 17:3002/12/23 05:4002/12/23 21:1502/12/23 21:15B1-102/12/23 21:4503/12/23 20:3003/12/23 21:40ADNOC-850
23Dec-2313551ADNOC-22902/12/23 20:5002/12/23 18:3002/12/23 18:3703/12/23 01:0003/12/23 01:00B6-103/12/23 02:5504/12/23 13:3004/12/23 18:25ADNOC-851
24Dec-2313552JOPETWIL-6802/12/23 10:3002/12/23 17:2202/12/23 12:0002/12/23 22:5502/12/23 22:55B12-102/12/23 22:5503/12/23 22:0003/12/23 22:00ADNOC-A02
25Dec-2313553A-GRACE02/12/23 18:0002/12/23 20:0002/12/23 20:4003/12/23 01:2003/12/23 01:20B13-103/12/23 02:2503/12/23 12:2503/12/23 12:45ADNOC-A03
26Dec-2313554ADNOC-A0502/12/23 19:5002/12/23 21:2002/12/23 21:5103/12/23 01:4503/12/23 01:45B14-103/12/23 02:2503/12/23 20:3003/12/23 21:25ADNOC-A04
27Dec-2313555LCT-TARFFAH-103/12/23 10:0002/12/23 21:5003/12/23 06:5503/12/23 23:1503/12/23 23:15B3-103/12/23 23:4504/12/23 13:4004/12/23 14:15ADNOC-A05
28Dec-2313556M-SUPPORTER03/12/23 02:1003/12/23 00:5003/12/23 01:5003/12/23 06:3503/12/23 06:3503/12/23 12:5503/12/23 20:3003/12/23 20:50ADNOC-S01
29Dec-2313557LCT-JEWAHER-103/12/23 00:1003/12/23 02:0003/12/23 02:1003/12/23 05:5503/12/23 05:55B7-103/12/23 07:3504/12/23 11:0004/12/23 11:40ADNOC-S02
30Dec-2313558ADNOC-51103/12/23 00:3003/12/23 08:4803/12/23 08:5103/12/23 18:1003/12/23 18:10B5-103/12/23 19:3003/12/23 21:1503/12/23 22:00A-CHLOE
31Dec-2313559CREST RADIANT-503/12/23 05:0003/12/23 09:5503/12/23 10:0203/12/23 15:3003/12/23 15:30B2-103/12/23 16:0503/12/23 20:5804/12/23 08:40A-GRACE
32Dec-2313560WARDEH-103/12/23 11:0003/12/23 11:1503/12/23 22:3503/12/23 22:35B12-103/12/23 23:2004/12/23 11:2004/12/23 12:55A-LIBERTY
33Dec-2313561Z-OCEAN03/12/23 18:0003/12/23 13:1703/12/23 13:1603/12/23 21:0003/12/23 21:00B13-103/12/23 22:1804/12/23 01:4504/12/23 02:07AMS-ONYX
34Dec-2313562ADNOC-23003/12/23 15:0003/12/23 17:5003/12/23 18:3003/12/23 22:3003/12/23 22:30B1-103/12/23 23:0504/12/23 23:4005/12/23 00:15AMS-RUBY
2023-Vessel Onshore Operations
Cell Formulas
RangeFormula
B4:B34B4=IFERROR(EOMONTH(K4,-1)+1,"")
C4:C34C4=IFERROR(MATCH(DAY(K4),{1,8,15,22,29}),"")
Q7Q7=P7-O7
Cells with Data Validation
CellAllowCriteria
U2List=Info_Settings!$B$3:$B$99
X2List=Info_Settings!$B$3:$B$16
 
Upvote 0

Forum statistics

Threads
1,216,500
Messages
6,131,015
Members
449,615
Latest member
Nic0la

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top