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

[Lukma]

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
	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT
14	Wk No's	Monthly Operation Criteria	Mus-No	Supply Vessel	D/P VSL	Vls Deck	Total Plan	FWB Date & Time	Manifest Date & Time	ADNOC Permission	Arrive Date & Time	Commence Cargo OP	Complete Cargo OP	Vessel Depart AD-Jetty	Ms't on Time YES / NO
15	53	01-Jan-21	3705	ADNOC-810	P	Half	2	31/12/20 21:00	31/12/20 11:05	31/12/20 21:34	01/01/21 00:50	01/01/21 03:00	01/01/21 09:00	01/01/21 09:30	YES
16	53	01-Dec-20	3706	ADNOC-224	D	Half	5	31/12/20 00:45	31/12/20 11:30	31/12/20 19:09	31/12/20 23:00	01/01/21 04:00	01/01/21 18:25	01/01/21 18:48	NO
17	0	01-Jan-21	3666-O	ASL SWIFT	0	Half	2	01/01/21 06:30			01/01/21 11:30	01/01/21 11:40	02/01/21 03:40	Off-Hire	YES
18	53	01-Jan-21	3707	Z-POWER	D	Half	3	30/12/20 21:25	30/12/20 22:00	31/12/20 22:08	01/01/21 01:15	01/01/21 03:00	01/01/21 11:25	01/01/21 19:00	NO
19	53	01-Jan-21	3708	SMIT LUZON	D	Full	1	31/12/20 22:42	31/12/20 13:35	01/01/21 04:14	01/01/21 08:30	01/01/21 08:42	01/01/21 19:54	01/01/21 20:30	YES
20	53	01-Jan-21	3709	SMIT LUMUT	D	Half	3	31/12/20 22:30	31/12/20 18:00	31/12/20 23:00	01/01/21 05:20	01/01/21 07:58	01/01/21 17:45	01/01/21 19:15	NO
21	53	01-Jan-21	3710	B-LIBERTY-313	D	NAF	NAF	01/01/21 05:30	31/12/20 19:30	01/01/21 11:30	01/01/21 15:09	02/01/21 00:00	02/01/21 12:05	02/01/21 12:45	YES
22	53	01-Jan-21	3711	MAC PHOENIX	0	Half	2	31/12/20 21:42	31/12/20 20:35	01/01/21 05:05	01/01/21 09:45	01/01/21 13:30	01/01/21 20:24	01/01/21 21:22	NO
23	53	01-Jan-21	3712	ADNOC-812	P	Half	2	01/01/21 01:30	31/12/20 21:00	01/01/21 04:45	01/01/21 08:05	01/01/21 08:25	01/01/21 20:25	01/01/21 21:15	NO
24	53	01-Jan-21	3713	A-HERCULES	0	Half	3	01/01/21 04:00	31/12/20 21:30	01/01/21 09:58	01/01/21 13:50	01/01/21 15:45	01/01/21 23:40	02/01/21 00:30	YES
25	1	01-Jan-21	1	LCT-TARFFAH	P	Full	1	02/01/21 11:00	01/01/21 11:45	02/01/21 12:39	02/01/21 16:00	02/01/21 20:00	03/01/21 05:45	03/01/21 07:10	YES
26	1	01-Jan-21	2	ADNOC-850	P	Half	2	02/01/21 20:10	01/01/21 13:30	02/01/21 20:35	03/01/21 00:15	03/01/21 03:25	03/01/21 12:45	03/01/21 13:15	YES
27	1	01-Jan-21	3	ADNOC-510	D	Full	1	01/01/21 03:24	01/01/21 12:52	01/01/21 19:05	02/01/21 00:00	02/01/21 00:15	02/01/21 14:00	02/01/21 14:20	NO
28	1	01-Jan-21	4	A-GRACE	D	Full	1	01/01/21 05:02	01/01/21 13:38	01/01/21 17:06	01/01/21 21:40	01/01/21 23:50	02/01/21 05:35	02/01/21 06:15	NO
29	1	01-Jan-21	5	MARCAP-2	P	Full	1	01/01/21 21:30	01/01/21 14:15	02/01/21 04:44	02/01/21 08:30	02/01/21 09:25	02/01/21 17:30	02/01/21 17:35	YES
30	1	01-Jan-21	6	QMS DELTA	D	Half	2	01/01/21 10:30	01/01/21 16:20	01/01/21 21:00	02/01/21 01:00	02/01/21 03:40	02/01/21 21:30	02/01/21 21:50	NO
31	1	01-Jan-21	7	ADNOC-1011	D	Half	3	31/12/20 21:45	01/01/21 17:00	02/01/21 05:52	02/01/21 10:25	02/01/21 15:35	03/01/21 02:20	03/01/21 03:40	NO
32	1	01-Jan-21	8	QMS NEPTUNE	D	Half	3	01/01/21 09:50	01/01/21 17:20	01/01/21 19:14	02/01/21 00:15	02/01/21 05:20	02/01/21 18:10	02/01/21 19:00	NO
33	1	01-Jan-21	9	ADNOC-221	D	Half	4	01/01/21 17:20	01/01/21 17:40	01/01/21 19:30	01/01/21 23:55	02/01/21 00:30	02/01/21 23:05	03/01/21 00:45	NO
34	1	01-Jan-21	10	SEACOR ALPS	D	Full	1	01/01/21 22:30	01/01/21 19:00	01/01/21 22:40	02/01/21 03:50	02/01/21 04:50	03/01/21 14:00	03/01/21 14:10	NO
35	1	01-Jan-21	11	ADNOC-229	D	NAF	NAF	01/01/21 16:30	01/01/21 22:10	01/01/21 16:30	01/01/21 17:30	02/01/21 00:00	02/01/21 22:08	02/01/21 23:00	NO
36	1	01-Jan-21	12	LCT-MARWAH-1	P	Full	1	02/01/21 05:00	02/01/21 09:20	02/01/21 09:35	02/01/21 13:05	02/01/21 15:45	03/01/21 00:10	03/01/21 00:50	NO
37	1	01-Jan-21	13	Z-OCEAN	P	Full	1	02/01/21 04:00	02/01/21 08:27	02/01/21 08:48	02/01/21 12:18	02/01/21 12:54	02/01/21 17:30	02/01/21 17:30	NO
38	1	01-Jan-21	14	ADNOC-230	D	NAF	NAF	02/01/21 22:15	02/01/21 13:19	03/01/21 00:45	03/01/21 04:00	03/01/21 04:25	04/01/21 05:30	04/01/21 16:30	YES
39	1	01-Jan-21	15	A-LIBERTY	D	Full	1	02/01/21 12:30	02/01/21 13:55	02/01/21 18:30	02/01/21 22:20	02/01/21 22:20	03/01/21 04:55	03/01/21 06:00	NO
40	2	01-Jan-21	16	ADNOC-851	P	Full	1	02/01/21 23:05	03/01/21 14:20	03/01/21 00:46	03/01/21 04:00	03/01/21 07:50	03/01/21 16:05	03/01/21 16:30	NO
41	1	01-Jan-21	17	A-CHLOE	D	Full	1	02/01/21 17:00	02/01/21 16:55	02/01/21 18:20	02/01/21 22:30	03/01/21 00:50	03/01/21 11:30	03/01/21 13:30	NO
42	1	01-Jan-21	18	ADNOC-225	D	NAF	NAF	02/01/21 21:45	02/01/21 20:50	02/01/21 22:10	03/01/21 02:00	03/01/21 03:50	03/01/21 23:59	04/01/21 00:45	NO
43	2	01-Jan-21	19	ADNOC-811	P	Half	2	03/01/21 02:15	03/01/21 08:20	03/01/21 08:50	03/01/21 12:05	03/01/21 12:30	03/01/21 19:55	03/01/21 21:00	NO
44	2	01-Jan-21	20	SWISSCO RUBY	P	Full	1	03/01/21 23:00	03/01/21 09:30	03/01/21 22:30	04/01/21 02:15	04/01/21 07:45	04/01/21 14:15	04/01/21 16:35	YES
45	2	01-Jan-21	21	ADNOC-512	D	Half	2	03/01/21 14:40	03/01/21 10:50	03/01/21 11:20	03/01/21 18:25	03/01/21 19:10	03/01/21 21:50	03/01/21 22:20	NO
46	2	01-Jan-21	22	AMS-RUBY	D	Half	3	03/01/21 18:15	03/01/21 11:45	03/01/21 16:18	03/01/21 23:30	04/01/21 00:05	04/01/21 18:25	04/01/21 19:30	YES
47	2	01-Jan-21	23	ADNOC-511	D	Half	4	03/01/21 02:50	03/01/21 17:18	03/01/21 12:46	03/01/21 16:25	03/01/21 17:18	03/01/21 23:25	03/01/21 23:50	NO
48	2	01-Jan-21	24	ADNOC-810	P	Half	3	03/01/21 22:15	03/01/21 11:30	03/01/21 18:15	04/01/21 02:10	04/01/21 02:35	04/01/21 07:30	04/01/21 16:20	YES
49	2	01-Jan-21	25	ADNOC-812	P	Half	2	03/01/21 21:00	03/01/21 11:50	03/01/21 21:30	04/01/21 00:15	04/01/21 00:30	04/01/21 05:10	04/01/21 16:20	YES
ILSP Vessel Activity Tracking

Cell Formulas
Range	Formula
AF15:AF49	AF15	=IFERROR(WEEKNUM([@[Manifest Date & Time]]),"")
AG15:AG49	AG15	=IFERROR(EOMONTH([@[Arrive Date & Time]],-1)+1,"")
AJ15:AJ49	AJ15	=IFERROR(IF(VLOOKUP([@[Supply Vessel]],Vessels,1,0)=[@[Supply Vessel]],VLOOKUP([@[Supply Vessel]],Vessels,2,0),NA()),"")
AK15:AK49	AK15	=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:AT49	AT15	=IFERROR(IF(AN15>AM15-TIME(6,0,0),"NO","YES"),"")

 

[Fluff]

How about

=IFNA(INT(INDEX(AP16:AP100,MATCH(AI15,AI16:AI100,0)))-INT(AS15),"")

 

[Lukma]

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
	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW
14	Wk No's	Monthly Operation Criteria	Mus-No	Supply Vessel	D/P VSL	Vls Deck	Total Plan	FWB Date & Time	Manifest Date & Time	ADNOC Permission	Arrive Date & Time	Commence Cargo OP	Complete Cargo OP	Vessel Depart AD-Jetty	Ms't on Time YES / NO	Vessels	Return Date & Time	Total Days Spent
15	53	01-Jan-21	3705	ADNOC-810	P	Half	2	31/12/20 21:00	31/12/20 11:05	31/12/20 21:34	01/01/21 00:50	01/01/21 03:00	01/01/21 09:00	01/01/21 09:30	YES	ADNOC-810	04/01/21 02:10
16	53	01-Dec-20	3706	ADNOC-224	D	Half	5	31/12/20 00:45	31/12/20 11:30	31/12/20 19:09	31/12/20 23:00	01/01/21 04:00	01/01/21 18:25	01/01/21 18:48	NO	ADNOC-224	05/01/21 17:48
17	0	01-Jan-21	3666-O	ASL SWIFT	D	Half	2	01/01/21 06:30			01/01/21 11:30	01/01/21 11:40	02/01/21 03:40	Off-Hire	YES
18	53	01-Jan-21	3707	Z-POWER	D	Half	3	30/12/20 21:25	30/12/20 22:00	31/12/20 22:08	01/01/21 01:15	01/01/21 03:00	01/01/21 11:25	01/01/21 19:00	NO
19	53	01-Jan-21	3708	SMIT LUZON	D	Full	1	31/12/20 22:42	31/12/20 13:35	01/01/21 04:14	01/01/21 08:30	01/01/21 08:42	01/01/21 19:54	01/01/21 20:30	YES
20	53	01-Jan-21	3709	SMIT LUMUT	D	Half	3	31/12/20 22:30	31/12/20 18:00	31/12/20 23:00	01/01/21 05:20	01/01/21 07:58	01/01/21 17:45	01/01/21 19:15	NO
21	53	01-Jan-21	3710	B-LIBERTY-313	D	NAF	NAF	01/01/21 05:30	31/12/20 19:30	01/01/21 11:30	01/01/21 15:09	02/01/21 00:00	02/01/21 12:05	02/01/21 12:45	YES
22	53	01-Jan-21	3711	MAC PHOENIX	D	Half	2	31/12/20 21:42	31/12/20 20:35	01/01/21 05:05	01/01/21 09:45	01/01/21 13:30	01/01/21 20:24	01/01/21 21:22	NO
23	53	01-Jan-21	3712	ADNOC-812	P	Half	2	01/01/21 01:30	31/12/20 21:00	01/01/21 04:45	01/01/21 08:05	01/01/21 08:25	01/01/21 20:25	01/01/21 21:15	NO
24	53	01-Jan-21	3713	A-HERCULES	D	Half	3	01/01/21 04:00	31/12/20 21:30	01/01/21 09:58	01/01/21 13:50	01/01/21 15:45	01/01/21 23:40	02/01/21 00:30	YES
25	1	01-Jan-21	1	LCT-TARFFAH	P	Full	1	02/01/21 11:00	01/01/21 11:45	02/01/21 12:39	02/01/21 16:00	02/01/21 20:00	03/01/21 05:45	03/01/21 07:10	YES
26	1	01-Jan-21	2	ADNOC-850	P	Half	2	02/01/21 20:10	01/01/21 13:30	02/01/21 20:35	03/01/21 00:15	03/01/21 03:25	03/01/21 12:45	03/01/21 13:15	YES
27	1	01-Jan-21	3	ADNOC-510	D	Full	1	01/01/21 03:24	01/01/21 12:52	01/01/21 19:05	02/01/21 00:00	02/01/21 00:15	02/01/21 14:00	02/01/21 14:20	NO
28	1	01-Jan-21	4	A-GRACE	D	Full	1	01/01/21 05:02	01/01/21 13:38	01/01/21 17:06	01/01/21 21:40	01/01/21 23:50	02/01/21 05:35	02/01/21 06:15	NO
29	1	01-Jan-21	5	MARCAP-2	P	Full	1	01/01/21 21:30	01/01/21 14:15	02/01/21 04:44	02/01/21 08:30	02/01/21 09:25	02/01/21 17:30	02/01/21 17:35	YES
30	1	01-Jan-21	6	QMS DELTA	D	Half	2	01/01/21 10:30	01/01/21 16:20	01/01/21 21:00	02/01/21 01:00	02/01/21 03:40	02/01/21 21:30	02/01/21 21:50	NO
31	1	01-Jan-21	7	ADNOC-1011	D	Half	3	31/12/20 21:45	01/01/21 17:00	02/01/21 05:52	02/01/21 10:25	02/01/21 15:35	03/01/21 02:20	03/01/21 03:40	NO
32	1	01-Jan-21	8	QMS NEPTUNE	D	Half	3	01/01/21 09:50	01/01/21 17:20	01/01/21 19:14	02/01/21 00:15	02/01/21 05:20	02/01/21 18:10	02/01/21 19:00	NO
33	1	01-Jan-21	9	ADNOC-221	D	Half	4	01/01/21 17:20	01/01/21 17:40	01/01/21 19:30	01/01/21 23:55	02/01/21 00:30	02/01/21 23:05	03/01/21 00:45	NO
34	1	01-Jan-21	10	SEACOR ALPS	D	Full	1	01/01/21 22:30	01/01/21 19:00	01/01/21 22:40	02/01/21 03:50	02/01/21 04:50	03/01/21 14:00	03/01/21 14:10	NO
35	1	01-Jan-21	11	ADNOC-229	D	NAF	NAF	01/01/21 16:30	01/01/21 22:10	01/01/21 16:30	01/01/21 17:30	02/01/21 00:00	02/01/21 22:08	02/01/21 23:00	NO
ILSP Vessel Activity Tracking

Cell Formulas
Range	Formula
AF15:AF35	AF15	=IFERROR(WEEKNUM([@[Manifest Date & Time]]),"")
AG15:AG35	AG15	=IFERROR(EOMONTH([@[Arrive Date & Time]],-1)+1,"")
AJ15:AJ35	AJ15	=IFERROR(IF(VLOOKUP([@[Supply Vessel]],Vessels,1,0)=[@[Supply Vessel]],VLOOKUP([@[Supply Vessel]],Vessels,2,0),NA()),"")
AK15:AK35	AK15	=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:AT35	AT15	=IFERROR(IF(AN15>AM15-TIME(6,0,0),"NO","YES"),"")

 

[Fluff]

As this is now a significantly different question, it needs a new thread. Thanks