Need help with formula next occurrence of specific name and total days spent before returning

[Lukma]

Hi Friend

I need help with a formula in COLUMN F, Column G, and Column H
I have my data with the names of the Vessels in Column C, and I also have the Departure Date and time in Column E, but I need to have the vessel's next occurrence in Column F, and the Return date & time in Port in Column D to get total Numbers of days spent outside before returning
For Example, I have Vessel Name in Cecilie-K Departed date & time from Port in column E and the Return Date & Time In Port in column D

Please i will appreciate someone with a formula, without using the Array formula it slows my report

Regards

OSMS-Onshore Jetty Operations vessels Tracking.xlsx
	B	C	D	E	F	G	H
4	Mus Num	Supply Vessel	Vessel Arrive ADNOC Port Date & Time	Vessel Depart Port Date & Time	Vessel Name	Return Date	Total Time in Days Spent Return
5	1153	ADNOC-230
6	1154	CECILIE-K	01/05/23 18:06	02/05/23 04:48	CECILIE-K	02/05/23 16:50	0.50
7	1155	Z-OCEAN	01/05/23 18:10	02/05/23 04:15
8	1156	SWISSCO RUBY	02/05/23 09:35	03/05/23 00:10
9	1157	LCT-SHEWELEH	01/05/23 23:05	02/05/23 04:20
10	1158	A-RANGER	02/05/23 00:25	02/05/23 04:50
11	1159	ADNOC-S02	02/05/23 03:00	02/05/23 20:10
12	1160	ADNOC-810	01/05/23 22:50	06/05/23 04:00
13	1161	ADNOC-511	02/05/23 02:35	02/05/23 07:50
14	1162	SK MAINSTAY	02/05/23 09:55	03/05/23 01:30
15	1163	MUTAWA-402	02/05/23 06:30	02/05/23 18:24
16	1164	ADNOC-512	02/05/23 11:55	05/05/23 11:30
17	1165	CECILIE-K	02/05/23 16:50	06/05/23 05:00
18	1166	Z-POWER	02/05/23 14:35	03/05/23 13:55
19	1167	ADNOC-811	02/05/23 15:30	05/05/23 15:15
20	1168	ADNOC-222	02/05/23 16:20	03/05/23 14:10
21	1169	SMIT LUMUT	02/05/23 23:59	03/05/23 17:10
22	1170	A-RADIANT-7	02/05/23 20:10	03/05/23 02:10
23	1171	LCT MARWAH-1	02/05/23 15:45	05/05/23 21:50
24	1172	ADNOC-851	03/05/23 10:10	05/05/23 12:05
25	1173	AMS-ONYX	02/05/23 22:45	03/05/23 14:05
26	1174	ATLAS-SAPPHIRE	02/05/23 21:20	03/05/23 14:00
27	1175	M-SUPPORTER	03/05/23 05:20	04/05/23 14:15
28	1176	Z-OCEAN	02/05/23 22:35	03/05/23 13:00
29	1177	LCT-SHEWELEH	03/05/23 02:50	03/05/23 14:15
Sheet1

 

[aRandomHelper]

Assuming your data is already chronologically sorted, this might work:

Book3
	A	B	C	D	E	F	G
1	Mus Num	Supply Vessel	Vessel Arrive ADNOC Port Date & Time	Vessel Depart Port Date & Time	Vessel Name	Return Date	Total Time in Days Spent Return
2	1153	ADNOC-230			#N/A	#N/A	#N/A
3	1154	CECILIE-K	01/05/23 6:06 PM	02/05/23 4:48 AM	CECILIE-K	02-05-23 4:50 PM	0.5
4	1155	Z-OCEAN	01/05/23 6:10 PM	02/05/23 4:15 AM	Z-OCEAN	02-05-23 10:35 PM	0.8
5	1156	SWISSCO RUBY	02/05/23 9:35 AM	03/05/23 12:10 AM	#N/A	#N/A	#N/A
6	1157	LCT-SHEWELEH	01/05/23 11:05 PM	02/05/23 4:20 AM	LCT-SHEWELEH	03-05-23 2:50 AM	0.9
7	1158	A-RANGER	02/05/23 12:25 AM	02/05/23 4:50 AM	#N/A	#N/A	#N/A
8	1159	ADNOC-S02	02/05/23 3:00 AM	02/05/23 8:10 PM	#N/A	#N/A	#N/A
9	1160	ADNOC-810	01/05/23 10:50 PM	06/05/23 4:00 AM	#N/A	#N/A	#N/A
10	1161	ADNOC-511	02/05/23 2:35 AM	02/05/23 7:50 AM	#N/A	#N/A	#N/A
11	1162	SK MAINSTAY	02/05/23 9:55 AM	03/05/23 1:30 AM	#N/A	#N/A	#N/A
12	1163	MUTAWA-402	02/05/23 6:30 AM	02/05/23 6:24 PM	#N/A	#N/A	#N/A
13	1164	ADNOC-512	02/05/23 11:55 AM	05/05/23 11:30 AM	#N/A	#N/A	#N/A
14	1165	CECILIE-K	02/05/23 4:50 PM	06/05/23 5:00 AM	#N/A	#N/A	#N/A
15	1166	Z-POWER	02/05/23 2:35 PM	03/05/23 1:55 PM	#N/A	#N/A	#N/A
16	1167	ADNOC-811	02/05/23 3:30 PM	05/05/23 3:15 PM	#N/A	#N/A	#N/A
17	1168	ADNOC-222	02/05/23 4:20 PM	03/05/23 2:10 PM	#N/A	#N/A	#N/A
18	1169	SMIT LUMUT	02/05/23 11:59 PM	03/05/23 5:10 PM	#N/A	#N/A	#N/A
19	1170	A-RADIANT-7	02/05/23 8:10 PM	03/05/23 2:10 AM	#N/A	#N/A	#N/A
20	1171	LCT MARWAH-1	02/05/23 3:45 PM	05/05/23 9:50 PM	#N/A	#N/A	#N/A
21	1172	ADNOC-851	03/05/23 10:10 AM	05/05/23 12:05 PM	#N/A	#N/A	#N/A
22	1173	AMS-ONYX	02/05/23 10:45 PM	03/05/23 2:05 PM	#N/A	#N/A	#N/A
23	1174	ATLAS-SAPPHIRE	02/05/23 9:20 PM	03/05/23 2:00 PM	#N/A	#N/A	#N/A
24	1175	M-SUPPORTER	03/05/23 5:20 AM	04/05/23 2:15 PM	#N/A	#N/A	#N/A
25	1176	Z-OCEAN	02/05/23 10:35 PM	03/05/23 1:00 PM	#N/A	#N/A	#N/A
26	1177	LCT-SHEWELEH	03/05/23 2:50 AM	03/05/23 2:15 PM	#N/A	#N/A	#N/A
Sheet1

Cell Formulas
Range	Formula
E2:E26	E2	=VLOOKUP($B2,$B3:$D$1000,1,0)
F2:F26	F2	=VLOOKUP($B2,$B3:$D$1000,2,0)
G2:G26	G2	=F2-D2

 

[Lukma]

Dear Arandom

Many Thanks, it really works out for me

Regards​