Formula to calculate total numbers of days Spent from Departure Date& Time and Arrival Date and time

Lukma

Active Member
Joined
Feb 12, 2020
Messages
259
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Hi Friend

I need a solution which am finding slit difficult with a formula

For example in my Data Sheet

I have vessel name listed Column AI21 and i have the Departure time in column AS21
So let use a vessel as an example ADNOC-810 Departure date& Time in Column AS21 01/01/21 09:30
Return Date and Time to Jetty ADNOC-810 Column AP48 04/01/21 02:10

Now i need a help with a solution that will count the total numbers of days she spent outside before returning back to Jetty

Please i would to know how i can get this done or a better solution

2021 Planning Supply Chain Vessel Activity and Voyage Log Sheet.xlsx
AFAGAHAIAJAKALAMANAOAPAQARASAT
14Wk No'sMonthly Operation CriteriaMus-NoSupply VesselD/P VSLVls DeckTotal PlanFWB Date & TimeManifest Date & TimeADNOC PermissionArrive Date & TimeCommence Cargo OPComplete Cargo OPVessel Depart AD-JettyMs't on Time YES / NO
155301-Jan-213705ADNOC-810PHalf231/12/20 21:0031/12/20 11:0531/12/20 21:3401/01/21 00:5001/01/21 03:0001/01/21 09:0001/01/21 09:30YES
165301-Dec-203706ADNOC-224DHalf531/12/20 00:4531/12/20 11:3031/12/20 19:0931/12/20 23:0001/01/21 04:0001/01/21 18:2501/01/21 18:48NO
17001-Jan-213666-OASL SWIFT0Half201/01/21 06:3001/01/21 11:3001/01/21 11:4002/01/21 03:40Off-HireYES
185301-Jan-213707Z-POWERDHalf330/12/20 21:2530/12/20 22:0031/12/20 22:0801/01/21 01:1501/01/21 03:0001/01/21 11:2501/01/21 19:00NO
195301-Jan-213708SMIT LUZONDFull 131/12/20 22:4231/12/20 13:3501/01/21 04:1401/01/21 08:3001/01/21 08:4201/01/21 19:5401/01/21 20:30YES
205301-Jan-213709SMIT LUMUTDHalf331/12/20 22:3031/12/20 18:0031/12/20 23:0001/01/21 05:2001/01/21 07:5801/01/21 17:4501/01/21 19:15NO
215301-Jan-213710B-LIBERTY-313DNAFNAF01/01/21 05:3031/12/20 19:3001/01/21 11:3001/01/21 15:0902/01/21 00:0002/01/21 12:0502/01/21 12:45YES
225301-Jan-213711MAC PHOENIX0Half231/12/20 21:4231/12/20 20:3501/01/21 05:0501/01/21 09:4501/01/21 13:3001/01/21 20:2401/01/21 21:22NO
235301-Jan-213712ADNOC-812PHalf201/01/21 01:3031/12/20 21:0001/01/21 04:4501/01/21 08:0501/01/21 08:2501/01/21 20:2501/01/21 21:15NO
245301-Jan-213713A-HERCULES0Half301/01/21 04:0031/12/20 21:3001/01/21 09:5801/01/21 13:5001/01/21 15:4501/01/21 23:4002/01/21 00:30YES
25101-Jan-211LCT-TARFFAHPFull 102/01/21 11:0001/01/21 11:4502/01/21 12:3902/01/21 16:0002/01/21 20:0003/01/21 05:4503/01/21 07:10YES
26101-Jan-212ADNOC-850PHalf202/01/21 20:1001/01/21 13:3002/01/21 20:3503/01/21 00:1503/01/21 03:2503/01/21 12:4503/01/21 13:15YES
27101-Jan-213ADNOC-510DFull 101/01/21 03:2401/01/21 12:5201/01/21 19:0502/01/21 00:0002/01/21 00:1502/01/21 14:0002/01/21 14:20NO
28101-Jan-214A-GRACEDFull 101/01/21 05:0201/01/21 13:3801/01/21 17:0601/01/21 21:4001/01/21 23:5002/01/21 05:3502/01/21 06:15NO
29101-Jan-215MARCAP-2PFull 101/01/21 21:3001/01/21 14:1502/01/21 04:4402/01/21 08:3002/01/21 09:2502/01/21 17:3002/01/21 17:35YES
30101-Jan-216QMS DELTADHalf201/01/21 10:3001/01/21 16:2001/01/21 21:0002/01/21 01:0002/01/21 03:4002/01/21 21:3002/01/21 21:50NO
31101-Jan-217ADNOC-1011DHalf331/12/20 21:4501/01/21 17:0002/01/21 05:5202/01/21 10:2502/01/21 15:3503/01/21 02:2003/01/21 03:40NO
32101-Jan-218QMS NEPTUNEDHalf301/01/21 09:5001/01/21 17:2001/01/21 19:1402/01/21 00:1502/01/21 05:2002/01/21 18:1002/01/21 19:00NO
33101-Jan-219ADNOC-221DHalf401/01/21 17:2001/01/21 17:4001/01/21 19:3001/01/21 23:5502/01/21 00:3002/01/21 23:0503/01/21 00:45NO
34101-Jan-2110SEACOR ALPSDFull 101/01/21 22:3001/01/21 19:0001/01/21 22:4002/01/21 03:5002/01/21 04:5003/01/21 14:0003/01/21 14:10NO
35101-Jan-2111ADNOC-229DNAFNAF01/01/21 16:3001/01/21 22:1001/01/21 16:3001/01/21 17:3002/01/21 00:0002/01/21 22:0802/01/21 23:00NO
36101-Jan-2112LCT-MARWAH-1PFull 102/01/21 05:0002/01/21 09:2002/01/21 09:3502/01/21 13:0502/01/21 15:4503/01/21 00:1003/01/21 00:50NO
37101-Jan-2113Z-OCEANPFull 102/01/21 04:0002/01/21 08:2702/01/21 08:4802/01/21 12:1802/01/21 12:5402/01/21 17:3002/01/21 17:30NO
38101-Jan-2114ADNOC-230DNAFNAF02/01/21 22:1502/01/21 13:1903/01/21 00:4503/01/21 04:0003/01/21 04:2504/01/21 05:3004/01/21 16:30YES
39101-Jan-2115A-LIBERTY DFull 102/01/21 12:3002/01/21 13:5502/01/21 18:3002/01/21 22:2002/01/21 22:2003/01/21 04:5503/01/21 06:00NO
40201-Jan-2116ADNOC-851PFull 102/01/21 23:0503/01/21 14:2003/01/21 00:4603/01/21 04:0003/01/21 07:5003/01/21 16:0503/01/21 16:30NO
41101-Jan-2117A-CHLOEDFull 102/01/21 17:0002/01/21 16:5502/01/21 18:2002/01/21 22:3003/01/21 00:5003/01/21 11:3003/01/21 13:30NO
42101-Jan-2118ADNOC-225DNAFNAF02/01/21 21:4502/01/21 20:5002/01/21 22:1003/01/21 02:0003/01/21 03:5003/01/21 23:5904/01/21 00:45NO
43201-Jan-2119ADNOC-811PHalf203/01/21 02:1503/01/21 08:2003/01/21 08:5003/01/21 12:0503/01/21 12:3003/01/21 19:5503/01/21 21:00NO
44201-Jan-2120SWISSCO RUBYPFull 103/01/21 23:0003/01/21 09:3003/01/21 22:3004/01/21 02:1504/01/21 07:4504/01/21 14:1504/01/21 16:35YES
45201-Jan-2121ADNOC-512DHalf203/01/21 14:4003/01/21 10:5003/01/21 11:2003/01/21 18:2503/01/21 19:1003/01/21 21:5003/01/21 22:20NO
46201-Jan-2122AMS-RUBYDHalf303/01/21 18:1503/01/21 11:4503/01/21 16:1803/01/21 23:3004/01/21 00:0504/01/21 18:2504/01/21 19:30YES
47201-Jan-2123ADNOC-511DHalf403/01/21 02:5003/01/21 17:1803/01/21 12:4603/01/21 16:2503/01/21 17:1803/01/21 23:2503/01/21 23:50NO
48201-Jan-2124ADNOC-810PHalf303/01/21 22:1503/01/21 11:3003/01/21 18:1504/01/21 02:1004/01/21 02:3504/01/21 07:3004/01/21 16:20YES
49201-Jan-2125ADNOC-812PHalf203/01/21 21:0003/01/21 11:5003/01/21 21:3004/01/21 00:1504/01/21 00:3004/01/21 05:1004/01/21 16:20YES
ILSP Vessel Activity Tracking
Cell Formulas
RangeFormula
AF15:AF49AF15=IFERROR(WEEKNUM([@[Manifest Date & Time]]),"")
AG15:AG49AG15=IFERROR(EOMONTH([@[Arrive Date & Time]],-1)+1,"")
AJ15:AJ49AJ15=IFERROR(IF(VLOOKUP([@[Supply Vessel]],Vessels,1,0)=[@[Supply Vessel]],VLOOKUP([@[Supply Vessel]],Vessels,2,0),NA()),"")
AK15:AK49AK15=IFERROR(IF(AL15=UniqueList!$P$2,"M",IF(AL15=UniqueList!$P$3,"NAF",IF(AL15=""," ",IF(AL15>1,"Half",IF(AL15<1,"Half","Full "))))),"")
AT15:AT49AT15=IFERROR(IF(AN15>AM15-TIME(6,0,0),"NO","YES"),"")
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
How about
Excel Formula:
=IFNA(INT(INDEX(AP16:AP100,MATCH(AI15,AI16:AI100,0)))-INT(AS15),"")
 
Upvote 0
Hi Fluff

Thanks so much it works, but well i would like is there is a better way to extract the return name the vessel and date and time, for example i have created a 3 more column which is AU, AV, AW

Now let same my Supply Vessel List Name in Column AI continues repeating base on there voyage ( MUS No In Column AH ) for coming to the jetty
Now my Trouble is that How can i Create a formula that will extract the vessel and the next return date and time side by side with a formula so if am showing it to my boss he will be able to understand more

I have show an example of what i require in column AU & AV and AW will be the numbers of days

i Will appreciate if there is a way to Extract this

2021 Planning Supply Chain Vessel Activity and Voyage Log Sheet.xlsx
AFAGAHAIAJAKALAMANAOAPAQARASATAUAVAW
14Wk No'sMonthly Operation CriteriaMus-NoSupply VesselD/P VSLVls DeckTotal PlanFWB Date & TimeManifest Date & TimeADNOC PermissionArrive Date & TimeCommence Cargo OPComplete Cargo OPVessel Depart AD-JettyMs't on Time YES / NOVessels Return Date & Time Total Days Spent
155301-Jan-213705ADNOC-810PHalf231/12/20 21:0031/12/20 11:0531/12/20 21:3401/01/21 00:5001/01/21 03:0001/01/21 09:0001/01/21 09:30YESADNOC-81004/01/21 02:10
165301-Dec-203706ADNOC-224DHalf531/12/20 00:4531/12/20 11:3031/12/20 19:0931/12/20 23:0001/01/21 04:0001/01/21 18:2501/01/21 18:48NOADNOC-22405/01/21 17:48
17001-Jan-213666-OASL SWIFTDHalf201/01/21 06:3001/01/21 11:3001/01/21 11:4002/01/21 03:40Off-HireYES
185301-Jan-213707Z-POWERDHalf330/12/20 21:2530/12/20 22:0031/12/20 22:0801/01/21 01:1501/01/21 03:0001/01/21 11:2501/01/21 19:00NO
195301-Jan-213708SMIT LUZONDFull 131/12/20 22:4231/12/20 13:3501/01/21 04:1401/01/21 08:3001/01/21 08:4201/01/21 19:5401/01/21 20:30YES
205301-Jan-213709SMIT LUMUTDHalf331/12/20 22:3031/12/20 18:0031/12/20 23:0001/01/21 05:2001/01/21 07:5801/01/21 17:4501/01/21 19:15NO
215301-Jan-213710B-LIBERTY-313DNAFNAF01/01/21 05:3031/12/20 19:3001/01/21 11:3001/01/21 15:0902/01/21 00:0002/01/21 12:0502/01/21 12:45YES
225301-Jan-213711MAC PHOENIXDHalf231/12/20 21:4231/12/20 20:3501/01/21 05:0501/01/21 09:4501/01/21 13:3001/01/21 20:2401/01/21 21:22NO
235301-Jan-213712ADNOC-812PHalf201/01/21 01:3031/12/20 21:0001/01/21 04:4501/01/21 08:0501/01/21 08:2501/01/21 20:2501/01/21 21:15NO
245301-Jan-213713A-HERCULESDHalf301/01/21 04:0031/12/20 21:3001/01/21 09:5801/01/21 13:5001/01/21 15:4501/01/21 23:4002/01/21 00:30YES
25101-Jan-211LCT-TARFFAHPFull 102/01/21 11:0001/01/21 11:4502/01/21 12:3902/01/21 16:0002/01/21 20:0003/01/21 05:4503/01/21 07:10YES
26101-Jan-212ADNOC-850PHalf202/01/21 20:1001/01/21 13:3002/01/21 20:3503/01/21 00:1503/01/21 03:2503/01/21 12:4503/01/21 13:15YES
27101-Jan-213ADNOC-510DFull 101/01/21 03:2401/01/21 12:5201/01/21 19:0502/01/21 00:0002/01/21 00:1502/01/21 14:0002/01/21 14:20NO
28101-Jan-214A-GRACEDFull 101/01/21 05:0201/01/21 13:3801/01/21 17:0601/01/21 21:4001/01/21 23:5002/01/21 05:3502/01/21 06:15NO
29101-Jan-215MARCAP-2PFull 101/01/21 21:3001/01/21 14:1502/01/21 04:4402/01/21 08:3002/01/21 09:2502/01/21 17:3002/01/21 17:35YES
30101-Jan-216QMS DELTADHalf201/01/21 10:3001/01/21 16:2001/01/21 21:0002/01/21 01:0002/01/21 03:4002/01/21 21:3002/01/21 21:50NO
31101-Jan-217ADNOC-1011DHalf331/12/20 21:4501/01/21 17:0002/01/21 05:5202/01/21 10:2502/01/21 15:3503/01/21 02:2003/01/21 03:40NO
32101-Jan-218QMS NEPTUNEDHalf301/01/21 09:5001/01/21 17:2001/01/21 19:1402/01/21 00:1502/01/21 05:2002/01/21 18:1002/01/21 19:00NO
33101-Jan-219ADNOC-221DHalf401/01/21 17:2001/01/21 17:4001/01/21 19:3001/01/21 23:5502/01/21 00:3002/01/21 23:0503/01/21 00:45NO
34101-Jan-2110SEACOR ALPSDFull 101/01/21 22:3001/01/21 19:0001/01/21 22:4002/01/21 03:5002/01/21 04:5003/01/21 14:0003/01/21 14:10NO
35101-Jan-2111ADNOC-229DNAFNAF01/01/21 16:3001/01/21 22:1001/01/21 16:3001/01/21 17:3002/01/21 00:0002/01/21 22:0802/01/21 23:00NO
ILSP Vessel Activity Tracking
Cell Formulas
RangeFormula
AF15:AF35AF15=IFERROR(WEEKNUM([@[Manifest Date & Time]]),"")
AG15:AG35AG15=IFERROR(EOMONTH([@[Arrive Date & Time]],-1)+1,"")
AJ15:AJ35AJ15=IFERROR(IF(VLOOKUP([@[Supply Vessel]],Vessels,1,0)=[@[Supply Vessel]],VLOOKUP([@[Supply Vessel]],Vessels,2,0),NA()),"")
AK15:AK35AK15=IFERROR(IF(AL15=UniqueList!$P$2,"M",IF(AL15=UniqueList!$P$3,"NAF",IF(AL15=""," ",IF(AL15>1,"Half",IF(AL15<1,"Half","Full "))))),"")
AT15:AT35AT15=IFERROR(IF(AN15>AM15-TIME(6,0,0),"NO","YES"),"")
 
Upvote 0
As this is now a significantly different question, it needs a new thread. Thanks
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

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