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

Lukma said:

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
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
2																			Month Filter >>>	December 2021
3	Month	WK NUM	Mus No	Supply Vessel	Vessel Arrived FWB Date&Time	Manifest Date & Time	Control Tower Permission Date & Time	Berth Position Date & Time	Berth Nos	Vessel Arrive Secured Berth Date & Time	Berth Nos	Start Cargo Deck offload&Load	Complete Date & Time	Departure Adnoc Port Date & Time	Vessel Return Date & Time	Total Spent Days			Vessels Name	Total Nos Of Trips	Wk-1	Wk-2	Wk-3	Wk-4	Wk-5	Average Trip
4	Dec-23	1	3532	MUTAWA-304	29/11/23 01:00	30/11/23 10:50	30/11/23 19:00	01/12/23 00:30		01/12/23 00:30	B10-1	01/12/23 00:55	01/12/23 04:30	01/12/23 13:15					ADNOC-1010
5	Nov-23	5	3533	LCT-TARFFAH-1	29/11/23 10:30	30/11/23 11:35	30/11/23 11:45	30/11/23 15:45	B3-2	30/11/23 19:20	B3-1	30/11/23 20:00	01/12/23 00:10	01/12/23 01:45					ADNOC-1011
6	Dec-23	1	3534	SWISSCO RUBY	29/11/23 01:50	30/11/23 13:20	29/11/23 22:20	01/12/23 02:00		01/12/23 02:00	B6-1	01/12/23 02:55	01/12/23 16:30	01/12/23 17:30					ADNOC-221
7	Dec-23	1	3535	ADNOC-230	30/11/23 14:30	30/11/23 16:33	30/11/23 19:25	01/12/23 00:20		01/12/23 00:20	B1-1	01/12/23 00:55	01/12/23 17:00	01/12/23 18:30	03/12/23 22:30	2.2			ADNOC-222
8	Dec-23	1	3536	WARDEH-1	30/11/23 19:20	30/11/23 19:15	01/12/23 03:40	01/12/23 07:10		01/12/23 07:10	B11-1	01/12/23 08:30	01/12/23 21:10	01/12/23 22:00					ADNOC-223
9	Nov-23	5	3537	LCT RAZAN-1	29/11/23 01:50	30/11/23 20:19	29/11/23 02:20	30/11/23 22:45		30/11/23 22:45	B8-1	30/11/23 23:40	01/12/23 12:45	01/12/23 13:10					ADNOC-224
10	Dec-23	1	3538	STANFORD ALPHA	29/11/23 05:00	30/11/23 22:44	01/12/23 01:55	01/12/23 03:15		01/12/23 03:15	B9-1	01/12/23 04:48	01/12/23 22:40	01/12/23 23:05					ADNOC-225
11	Dec-23	1	3539	JOPETWIL-62	30/12/23 20:00	30/11/23 22:37	01/12/23 01:52	01/12/23 06:30		01/12/23 06:30	B3-1	01/12/23 06:30	01/12/23 15:55	01/12/23 17:00					ADNOC-226
12	Dec-23	1	3540	CECILIE-K	01/12/23 12:24	01/12/23 15:55	01/12/23 16:25	01/12/23 23:12		01/12/23 23:12	B14-1	01/12/23 23:54	02/12/23 12:24	02/12/23 12:48					ADNOC-227
13	Dec-23	1	3541	AD-ASTRA	01/12/23 18:00	01/12/23 16:30	01/12/23 16:52	01/12/23 23:25		01/12/23 23:25	RORO	01/12/23 23:35	01/12/23 23:47	01/12/23 23:59					ADNOC-228
14	Dec-23	1	3542	LCT BIMA	30/11/23 21:20	01/12/23 17:50	01/12/23 18:00	01/12/23 19:30		01/12/23 19:30		01/12/23 20:45	02/12/23 09:30	02/12/23 10:00					ADNOC-229
15	Dec-23	1	3543	ADNOC-224	02/12/23 04:30	01/12/23 19:45	02/12/23 04:30	02/12/23 08:35		02/12/23 08:35	B3-1	02/12/23 09:00	02/12/23 16:10	02/12/23 16:40					ADNOC-230
16	Dec-23	1	3544	LCT BUSHRA	02/12/23 12:37	01/12/23 21:30	01/12/23 22:38	02/12/23 17:36		02/12/23 17:36	B10-1	02/12/23 18:50	03/12/23 00:35	03/12/23 00:55					ADNOC-510
17	Dec-23	1	3545	VENTURE	02/12/23 00:15	01/12/23 22:35	01/12/23 22:38	02/12/23 04:25		02/12/23 04:25	B6-1	02/12/23 04:40	02/12/23 08:55	02/12/23 09:30					ADNOC-511
18	Dec-23	1	3546	A-CHLOE	02/12/23 02:30	02/12/23 00:40	02/12/23 01:00	02/12/23 06:15		02/12/23 06:15	B9-1	02/12/23 07:00	02/12/23 23:55	03/12/23 00:45					ADNOC-512
19	Dec-23	1	3547	ADNOC-1010	02/12/23 07:00	02/12/23 02:00	02/12/23 04:30	02/12/23 11:15		02/12/23 11:15	B8-1	02/12/23 11:35	02/12/23 20:45	02/12/23 21:35					ADNOC-810
20	Dec-23	1	3548	ADNOC-221	02/12/23 15:30	02/12/23 11:19	02/12/23 11:25	02/12/23 20:05		02/12/23 20:05	B5-1	02/12/23 20:30	03/12/23 08:20	03/12/23 09:00					ADNOC-811
21	Dec-23	1	3549	LCT MARWAH-1	02/12/23 14:00	02/12/23 13:30	02/12/23 13:33	02/12/23 18:15		02/12/23 18:15	B4-1	02/12/23 19:15	03/12/23 01:05	03/12/23 02:45					ADNOC-812
22	Dec-23	1	3550	ADNOC-226	02/12/23 16:55	02/12/23 17:30	02/12/23 05:40	02/12/23 21:15		02/12/23 21:15	B1-1	02/12/23 21:45	03/12/23 20:30	03/12/23 21:40					ADNOC-850
23	Dec-23	1	3551	ADNOC-229	02/12/23 20:50	02/12/23 18:30	02/12/23 18:37	03/12/23 01:00		03/12/23 01:00	B6-1	03/12/23 02:55	04/12/23 13:30	04/12/23 18:25					ADNOC-851
24	Dec-23	1	3552	JOPETWIL-68	02/12/23 10:30	02/12/23 17:22	02/12/23 12:00	02/12/23 22:55		02/12/23 22:55	B12-1	02/12/23 22:55	03/12/23 22:00	03/12/23 22:00					ADNOC-A02
25	Dec-23	1	3553	A-GRACE	02/12/23 18:00	02/12/23 20:00	02/12/23 20:40	03/12/23 01:20		03/12/23 01:20	B13-1	03/12/23 02:25	03/12/23 12:25	03/12/23 12:45					ADNOC-A03
26	Dec-23	1	3554	ADNOC-A05	02/12/23 19:50	02/12/23 21:20	02/12/23 21:51	03/12/23 01:45		03/12/23 01:45	B14-1	03/12/23 02:25	03/12/23 20:30	03/12/23 21:25					ADNOC-A04
27	Dec-23	1	3555	LCT-TARFFAH-1	03/12/23 10:00	02/12/23 21:50	03/12/23 06:55	03/12/23 23:15		03/12/23 23:15	B3-1	03/12/23 23:45	04/12/23 13:40	04/12/23 14:15					ADNOC-A05
28	Dec-23	1	3556	M-SUPPORTER	03/12/23 02:10	03/12/23 00:50	03/12/23 01:50	03/12/23 06:35		03/12/23 06:35		03/12/23 12:55	03/12/23 20:30	03/12/23 20:50					ADNOC-S01
29	Dec-23	1	3557	LCT-JEWAHER-1	03/12/23 00:10	03/12/23 02:00	03/12/23 02:10	03/12/23 05:55		03/12/23 05:55	B7-1	03/12/23 07:35	04/12/23 11:00	04/12/23 11:40					ADNOC-S02
30	Dec-23	1	3558	ADNOC-511	03/12/23 00:30	03/12/23 08:48	03/12/23 08:51	03/12/23 18:10		03/12/23 18:10	B5-1	03/12/23 19:30	03/12/23 21:15	03/12/23 22:00					A-CHLOE
31	Dec-23	1	3559	CREST RADIANT-5	03/12/23 05:00	03/12/23 09:55	03/12/23 10:02	03/12/23 15:30		03/12/23 15:30	B2-1	03/12/23 16:05	03/12/23 20:58	04/12/23 08:40					A-GRACE
32	Dec-23	1	3560	WARDEH-1		03/12/23 11:00	03/12/23 11:15	03/12/23 22:35		03/12/23 22:35	B12-1	03/12/23 23:20	04/12/23 11:20	04/12/23 12:55					A-LIBERTY
33	Dec-23	1	3561	Z-OCEAN	03/12/23 18:00	03/12/23 13:17	03/12/23 13:16	03/12/23 21:00		03/12/23 21:00	B13-1	03/12/23 22:18	04/12/23 01:45	04/12/23 02:07					AMS-ONYX
34	Dec-23	1	3562	ADNOC-230	03/12/23 15:00	03/12/23 17:50	03/12/23 18:30	03/12/23 22:30		03/12/23 22:30	B1-1	03/12/23 23:05	04/12/23 23:40	05/12/23 00:15					AMS-RUBY
2023-Vessel Onshore Operations

Cell Formulas
Range	Formula
B4:B34	B4	=IFERROR(EOMONTH(K4,-1)+1,"")
C4:C34	C4	=IFERROR(MATCH(DAY(K4),{1,8,15,22,29}),"")
Q7	Q7	=P7-O7

Cells with Data Validation
Cell	Allow	Criteria
U2	List	=Info_Settings!$B$3:$B$99
X2	List	=Info_Settings!$B$3:$B$16

Click to expand...