Formula to find Productive Time and Non-Productive time

[Lukma]

Hi Friends

Firstly I most extend my special thanks to every one, i have a question and to see if it is possible to extract such information from my Data range. for each supply Vessel productive time and Non Productive Time for every month for each Supply Vessel

Appreciate if anyone help out with a suitable means to get it done

Regards

ILSP Summary Data Reports.xlsx
	B	C	D	E	F	G	H	I	J
4	Month	Voyage	Supply Vessel			Location	Date Arrive	End Date	Total Hours
5	01-Jan-21	3705	ADNOC-810	P	Half	USSC	02/01/21 08:55	02/01/21 12:15	3:20
6	01-Jan-21	3705	ADNOC-810	P	Half	Jopetwill-300	02/01/21 13:35	02/01/21 19:50	6:15
7	01-Jan-21	3705	ADNOC-810	P	Half	USSC	02/01/21 20:25	02/01/21 21:30	1:05
8	01-Jan-21	3705	ADNOC-810	P	Half	USSC	03/01/21 07:45	03/01/21 10:45	3:00
9	01-Jan-21	3706	ADNOC-224	D	Half	Al Ghallan Island	02/01/21 07:10	02/01/21 12:18	5:08
10	01-Jan-21	3706	ADNOC-224	D	Half	Asseifiya Island	02/01/21 14:18	02/01/21 15:00	0:42
11	01-Jan-21	3706	ADNOC-224	D	Half	Ettouk Island	02/01/21 17:40	02/01/21 18:42	1:02
12	01-Jan-21	3706	ADNOC-224	D	Half	Umm Al Anbar	03/01/21 07:00	03/01/21 10:42	3:42
13	01-Jan-21	3706	ADNOC-224	D	Half	Deep Driller-3	04/01/21 04:18	04/01/21 16:42	12:24
14	01-Dec-20	3666-O	ASL SWIFT	D	Half	Rig Artabhatt-1	29/12/20 08:15	29/12/20 15:20	7:05
15	01-Dec-20	3666-O	ASL SWIFT	D	Half	Rig Al Bzoom	30/12/20 15:50	30/12/20 21:00	5:10
16	01-Dec-20	3666-O	ASL SWIFT	D	Half	Rig Artabhatt-1	31/12/20 04:00	31/12/20 18:30	14:30
17	01-Jan-21	3707	Z-POWER	D	Half	Rig Artabhatt-1	02/01/21 09:45	02/01/21 10:55	1:10
18	01-Jan-21	3707	Z-POWER	D	Half	Barge Pride	02/01/21 13:40	02/01/21 16:30	2:50
19	01-Jan-21	3707	Z-POWER	D	Half	Rig Diyina	02/01/21 20:45	03/01/21 02:40	5:55
20	01-Jan-21	3707	Z-POWER	D	Half	Deep Driller-3	03/01/21 10:15	03/01/21 12:10	1:55
21	01-Jan-21	3707	Z-POWER	D	Half	Deep Driller-3	03/01/21 20:10	04/01/21 04:00	7:50
22	01-Jan-21	3708	SMIT LUZON	D	Full	Rig Hudairiyat	02/01/21 16:18	04/01/21 16:30	48:12
23	01-Jan-21	3708	SMIT LUZON	D	Full	Rig Hudairiyat	04/01/21 21:00	05/01/21 11:54	14:54
24	01-Jan-21	3708	SMIT LUZON	D	Full	Barge Shamal	15/01/21 21:00	16/01/21 01:06	4:06
25	01-Jan-21	3709	SMIT LUMUT	D	Half	Rig Al Lulu	01/01/21 23:59	02/01/21 00:45	0:46
26	01-Jan-21	3709	SMIT LUMUT	D	Half	Rig Al Hail	02/01/21 11:25	02/01/21 13:00	1:35
27	01-Jan-21	3709	SMIT LUMUT	D	Half	Rig VKN-3	02/01/21 22:00	02/01/21 23:10	1:10
28	01-Jan-21	3709	SMIT LUMUT	D	Half	Rig Al Ghallan	03/01/21 08:35	04/01/21 03:45	19:10
29	01-Jan-21	3710	B-LIBERTY-313	D	NAF	Rig SMS Faith	03/01/21 06:00	03/01/21 08:48	2:48
30	01-Jan-21	3710	B-LIBERTY-313	D	NAF	Rig Al Gharbia	03/01/21 12:00	03/01/21 21:00	9:00
31	01-Jan-21	3710	B-LIBERTY-313	D	NAF	Rig Marawwah	04/01/21 00:35	04/01/21 14:50	14:15
32	01-Jan-21	3710	B-LIBERTY-313	D	NAF	Rig Yemillah	04/01/21 16:30	05/01/21 00:01	7:31
33	01-Jan-21	3710	B-LIBERTY-313	D	NAF	Rig Al Hail	05/01/21 05:30	05/01/21 20:00	14:30
34	01-Jan-21	3711	MAC PHOENIX	D	Half	Rig Muhaiyimat	02/01/21 04:13	02/01/21 15:45	11:32
35	01-Jan-21	3711	MAC PHOENIX	D	Half	Deep Driller-3	02/01/21 17:30	03/01/21 12:15	18:45
36	01-Jan-21	3712	ADNOC-812	P	Half	Barge Al Hyleh	02/01/21 09:45	02/01/21 18:10	8:25
37	01-Jan-21	3713	A-HERCULES	P	Half	Barge MB-1	02/01/21 10:45	02/01/21 15:50	5:05
38	01-Jan-21	3713	A-HERCULES	P	Half	Barge Pesto	03/01/21 08:30	03/01/21 13:25	4:55
39	01-Jan-21	3713	A-HERCULES	P	Half	ZWSC	04/01/21 08:40	04/01/21 09:20	0:40
40	01-Jan-21	3713	A-HERCULES	P	Half	ZCSC	04/01/21 13:40	04/01/21 17:45	4:05
41	01-Jan-21	3713	A-HERCULES	P	Half	ZCSC	04/01/21 15:53	04/01/21 17:45	1:52
42	01-Jan-21	3713	A-HERCULES	P	Half	Barge MB-1	04/01/21 20:00	04/01/21 20:30	0:30
43	01-Jan-21	3713	A-HERCULES	P	Half	ZWSC	05/01/21 08:40	05/01/21 11:45	3:05
44	01-Jan-21	1	LCT-TARFFAH	P	Full	Zirku Island	03/01/21 20:40	04/01/21 08:50	12:10
45	01-Jan-21	1	LCT-TARFFAH	P	Full	Ettouk Island	04/01/21 17:40	05/01/21 08:25	14:45
46	01-Jan-21	2	ADNOC-850	P	Half	ACPT	03/01/21 23:15	04/01/21 01:25	2:10
47	01-Jan-21	2	ADNOC-850	P	Half	UZWSP	04/01/21 02:50	04/01/21 03:45	0:55
48	01-Jan-21	2	ADNOC-850	P	Half	ACPT	04/01/21 08:30	04/01/21 18:30	10:00
49	01-Jan-21	2	ADNOC-850	P	Half	ACPT	05/01/21 07:25	05/01/21 11:50	4:25
50	01-Jan-21	2	ADNOC-850	P	Half	UZNSP	05/01/21 13:00	05/01/21 15:30	2:30
51	01-Jan-21	3	ADNOC-510	D	Full	Rig Sms Mariam	03/01/21 07:20	04/01/21 02:15	18:55
52	01-Jan-21	4	A-GRACE	D	Full	Rig Makasib	03/01/21 05:03	05/01/21 12:08	55:05
53	01-Jan-21	5	MARCAP-2	P	Full	ZWSC	03/01/21 07:30	03/01/21 14:20	6:50
54	01-Jan-21	5	MARCAP-2	P	Full	ZWSC	04/01/21 07:40	04/01/21 17:30	9:50
55	01-Jan-21	5	MARCAP-2	P	Full	Barge Pesto	04/01/21 18:08	04/01/21 18:55	0:47
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F46:F70	Cell Value	contains "NAF"	text	NO
F6:F45	Cell Value	contains "NAF"	text	NO
F5	Cell Value	contains "NAF"	text	NO

 

[J.Ty.]

HI,

How do you define "Productive time"?

Can it happen that a ship's activity starts in one month and ends in the following?

If yes, how should such cases be processed?

J.Ty.

 

[Lukma]

HI,

How do you define "Productive time"?

Can it happen that a ship's activity starts in one month and ends in the following?

If yes, how should such cases be processed?

J.Ty.

Hi J.Ty

thanks for responding to my question

The productive time is consider as the operation hours used at sites for offloading / Loading for every 24hrs

however am positing a sample of what i was previous using which is this

ILSP_Performance Tracking Data Rough Data entery.xlsx
	B	C	D	E	F	G	H	I	J
11	January			ADNOC-1010	ADNOC-1011	ADNOC-221	ADNOC-222	ADNOC-223	ADNOC-224
12	Supply Vessels Productive Time
13	Loading/ Offload ( Adnoc L&S Base )			1.8	3.1	2.7	0.8	1.5	3.0
14	Loading/ Offload (Free-Port)			0.0	0.1	0.0	0.0	0.0	0.0
15	In Transit / Steaming to Location			6.3	7.8	8.9	1.9	7.4	8.6
16	Loading/ Offload Jackup Rigs			0.0	0.0	2.1	0.8	1.4	2.5
17	Loading/ Offload Barges			0.0	0.0	0.7	0.0	0.0	0.1
18	Loading/ Offload Drilling / Artificial Islands			1.8	4.1	2.8	0.0	2.4	2.9
19	Loading/ Offload Production Island (DAS / Arzanah/ ZIRKU)			0.3	0.0	0.0	0.0	0.0	0.0
20	Loading/ Offload ( Complexes )			0.0	0.0	0.0	0.0	0.0	0.0
21	Others /marine/Rig Move			0.0	0.0	0.1	0.0	0.1	0.2
22	Sub Total			10.2	15.1	17.3	3.5	12.8	17.3
Summary_Report_Utilization

Cell Formulas
Range	Formula
E13:J13	E13	=IFERROR(SUMIFS('Vessel_Utilization Report'!$H$22:$H$600027,'Vessel_Utilization Report'!$D$22:$D$600027,Summary_Report_Utilization!E$11,'Vessel_Utilization Report'!$C$22:$C$600027,Summary_Report_Utilization!$B$11)/24,"")
E14:J14	E14	=IFERROR(SUMIFS('Vessel_Utilization Report'!$S$22:$S$600027,'Vessel_Utilization Report'!$D$22:$D$600027,Summary_Report_Utilization!E$11,'Vessel_Utilization Report'!$C$22:$C$600027,Summary_Report_Utilization!$B$11)/24,"")
E15:J15	E15	=IFERROR(SUMIFS('Vessel_Utilization Report'!$O$22:$O$600027,'Vessel_Utilization Report'!$D$22:$D$600027,Summary_Report_Utilization!E$11,'Vessel_Utilization Report'!$C$22:$C$600027,Summary_Report_Utilization!$B$11)/24,"")
E16:J16	E16	=IFERROR(SUMIFS('Vessel_Utilization Report'!$I$22:$I$600027,'Vessel_Utilization Report'!$D$22:$D$600027,Summary_Report_Utilization!E$11,'Vessel_Utilization Report'!$F$22:$F$600027,Summary_Report_Utilization!$C$2,'Vessel_Utilization Report'!$C$22:$C$600027,Summary_Report_Utilization!$B$11)/24,"")
E17:J17	E17	=IFERROR(SUMIFS('Vessel_Utilization Report'!$I$22:$I$600027,'Vessel_Utilization Report'!$D$22:$D$600027,Summary_Report_Utilization!E$11,'Vessel_Utilization Report'!$F$22:$F$600027,Summary_Report_Utilization!$C$3,'Vessel_Utilization Report'!$C$22:$C$600027,Summary_Report_Utilization!$B$11)/24,"")
E18:J18	E18	=IFERROR(SUMIFS('Vessel_Utilization Report'!$I$22:$I$600027,'Vessel_Utilization Report'!$D$22:$D$600027,Summary_Report_Utilization!E$11,'Vessel_Utilization Report'!$F$22:$F$600027,Summary_Report_Utilization!$C$4,'Vessel_Utilization Report'!$C$22:$C$600027,Summary_Report_Utilization!$B$11)/24,"")
E19:J19	E19	=IFERROR(SUMIFS('Vessel_Utilization Report'!$I$22:$I$600027,'Vessel_Utilization Report'!$D$22:$D$600027,Summary_Report_Utilization!E$11,'Vessel_Utilization Report'!$F$22:$F$600027,Summary_Report_Utilization!$C$5,'Vessel_Utilization Report'!$C$22:$C$600027,Summary_Report_Utilization!$B$11)/24,"")
E20:J20	E20	=IFERROR(SUMIFS('Vessel_Utilization Report'!$I$22:$I$600027,'Vessel_Utilization Report'!$D$22:$D$600027,Summary_Report_Utilization!E$11,'Vessel_Utilization Report'!$F$22:$F$600027,Summary_Report_Utilization!$C$6,'Vessel_Utilization Report'!$C$22:$C$600027,Summary_Report_Utilization!$B$11)/24,"")
E21:J21	E21	=IFERROR(SUMIFS('Vessel_Utilization Report'!$I$22:$I$600027,'Vessel_Utilization Report'!$D$22:$D$600027,Summary_Report_Utilization!E$11,'Vessel_Utilization Report'!$F$22:$F$600027,Summary_Report_Utilization!$C$7,'Vessel_Utilization Report'!$C$22:$C$600027,Summary_Report_Utilization!$B$11)/24,"")
E22:J22	E22	=SUM(E13:E21)

Cells with Data Validation
Cell	Allow	Criteria
B11:C11	List	=$KY$12:$KY$207

 

[Lukma]

Hi J Ty

This the continuity of the sample

ILSP_Performance Tracking Data Rough Data entery.xlsx
	B	C	D	E	F	G	H	I	J
23	Supply Vessel's Non-Productive Time
24	Waiting On Materials / Plan			0.0	0.0	0.2	0.0	0.0	0.0
25	Waiting On Late Cargo			0.0	0.0	0.0	0.0	0.0	0.0
26	Waiting on Berthing			1.9	3.0	2.5	0.8	1.4	1.7
27	Waiting For Adnoc L&S Base Operations Readines			5.0	1.7	3.1	0.6	2.9	1.6
28	Waiting For Free Port Readines			0.0	0.0	0.0	0.0	0.0	0.0
29	Waiting ON Jackup Rigs			0.0	0.0	0.5	0.5	0.2	2.5
30	Waiting ON Barges			0.0	0.0	0.4	0.0	0.1	0.4
31	Waiting On Drilling / Artificial Islands			1.5	6.5	4.9	0.0	4.2	2.4
32	Waiting On DayLight / Artificial Islands			0.0	0.0	0.0	0.0	0.0	0.0
33	Waiting On Production Island (DAS / Arzanah/ ZIRKU)			0.0	0.0	0.0	0.0	0.0	0.0
34	Waiting On Complexes			0.0	0.0	0.0	0.0	0.0	0.0
35	Waiting On Weather ( Wind Speed / Sea Condition )			3.7	4.4	1.5	1.2	1.1	3.3
36	Waiting on Weather (Fog / poor visibility)			0.0	0.0	0.0	0.0	0.1	1.4
37	Channel Closure			0.0	0.0	0.0	0.0	0.0	0.0
38	Waiting On VTS Permission			0.0	0.0	0.0	0.0	0.0	0.0
39	Sub Total			12.0	15.5	13.2	3.2	9.9	13.3
40	Port call For Routine Maintenance			8.7	0.0	0.0	24.3	8.0	0.0
41	Port Call For Crew Change / Supply
42	Sub- TOTAL			8.7	0.0	0.0	24.3	8.0	0.0
43	OFF HIRE			0	0	0	0	0	0
44	TOTAL Days			31	31	31	31	31	31
45
46	Vessel Availability			100%	100%	100%	100%	100%	100%
47
48	Productive Time			33%	49%	57%	11%	42%	57%
Summary_Report_Utilization

Cell Formulas
Range	Formula
E24:J24	E24	=IFERROR(SUMIFS('Vessel_Utilization Report'!$L$22:$L$600027,'Vessel_Utilization Report'!$D$22:$D$600027,Summary_Report_Utilization!E$11,'Vessel_Utilization Report'!$C$22:$C$600027,Summary_Report_Utilization!$B$11)/24,"")
E25:J25	E25	=IFERROR(SUMIFS('Vessel_Utilization Report'!$M$22:$M$600027,'Vessel_Utilization Report'!$D$22:$D$600027,Summary_Report_Utilization!E$11,'Vessel_Utilization Report'!$C$22:$C$600027,Summary_Report_Utilization!$B$11)/24,"")
E26:J26	E26	=IFERROR(SUMIFS('Vessel_Utilization Report'!$K$22:$K$600027,'Vessel_Utilization Report'!$D$22:$D$600027,Summary_Report_Utilization!E$11,'Vessel_Utilization Report'!$F$22:$F$600027,Summary_Report_Utilization!$D$2,'Vessel_Utilization Report'!$C$22:$C$600027,Summary_Report_Utilization!$B$11)/24,"")
E27:J27	E27	=IFERROR(SUMIFS('Vessel_Utilization Report'!$J$22:$J$600027,'Vessel_Utilization Report'!$D$22:$D$600027,Summary_Report_Utilization!E$11,'Vessel_Utilization Report'!$F$22:$F$600027,Summary_Report_Utilization!$D$2,'Vessel_Utilization Report'!$C$22:$C$600027,Summary_Report_Utilization!$B$11)/24,"")
E28:J28	E28	=IFERROR(SUMIFS('Vessel_Utilization Report'!$J$22:$J$600027,'Vessel_Utilization Report'!$D$22:$D$600027,Summary_Report_Utilization!E$11,'Vessel_Utilization Report'!$F$22:$F$600027,Summary_Report_Utilization!$D$3,'Vessel_Utilization Report'!$C$22:$C$600027,Summary_Report_Utilization!$B$11)/24,"")
E29:J29	E29	=IFERROR(SUMIFS('Vessel_Utilization Report'!$J$22:$J$600027,'Vessel_Utilization Report'!$D$22:$D$600027,Summary_Report_Utilization!E$11,'Vessel_Utilization Report'!$F$22:$F$600027,Summary_Report_Utilization!$C$2,'Vessel_Utilization Report'!$C$22:$C$600027,Summary_Report_Utilization!$B$11)/24,"")
E30:J30	E30	=IFERROR(SUMIFS('Vessel_Utilization Report'!$J$22:$J$600027,'Vessel_Utilization Report'!$D$22:$D$600027,Summary_Report_Utilization!E$11,'Vessel_Utilization Report'!$F$22:$F$600027,Summary_Report_Utilization!$C$3,'Vessel_Utilization Report'!$C$22:$C$600027,Summary_Report_Utilization!$B$11)/24,"")
E31:J31	E31	=IFERROR(SUMIFS('Vessel_Utilization Report'!$J$22:$J$600027,'Vessel_Utilization Report'!$D$22:$D$600027,Summary_Report_Utilization!E$11,'Vessel_Utilization Report'!$F$22:$F$600027,Summary_Report_Utilization!$C$4,'Vessel_Utilization Report'!$C$22:$C$600027,Summary_Report_Utilization!$B$11)/24,"")
E32:J32	E32	=IFERROR(SUMIFS('Vessel_Utilization Report'!$J$22:$J$600027,'Vessel_Utilization Report'!$D$22:$D$600027,Summary_Report_Utilization!E$11,'Vessel_Utilization Report'!$F$22:$F$600027,Summary_Report_Utilization!$C$8,'Vessel_Utilization Report'!$C$22:$C$600027,Summary_Report_Utilization!$B$11)/24,"")
E33:J33	E33	=IFERROR(SUMIFS('Vessel_Utilization Report'!$J$22:$J$600027,'Vessel_Utilization Report'!$D$22:$D$600027,Summary_Report_Utilization!E$11,'Vessel_Utilization Report'!$F$22:$F$600027,Summary_Report_Utilization!$C$5,'Vessel_Utilization Report'!$C$22:$C$600027,Summary_Report_Utilization!$B$11)/24,"")
E34:J34	E34	=IFERROR(SUMIFS('Vessel_Utilization Report'!$J$22:$J$600027,'Vessel_Utilization Report'!$D$22:$D$600027,Summary_Report_Utilization!E$11,'Vessel_Utilization Report'!$F$22:$F$600027,Summary_Report_Utilization!$C$6,'Vessel_Utilization Report'!$C$22:$C$600027,Summary_Report_Utilization!$B$11)/24,"")
E35:J35	E35	=IFERROR(SUMIFS('Vessel_Utilization Report'!$P$22:$P$600027,'Vessel_Utilization Report'!$D$22:$D$600027,Summary_Report_Utilization!E$11,'Vessel_Utilization Report'!$C$22:$C$600027,Summary_Report_Utilization!$B$11)/24,"")
E36:J36	E36	=IFERROR(SUMIFS('Vessel_Utilization Report'!$Q$22:$Q$600027,'Vessel_Utilization Report'!$D$22:$D$600027,Summary_Report_Utilization!E$11,'Vessel_Utilization Report'!$C$22:$C$600027,Summary_Report_Utilization!$B$11)/24,"")
E37:J37	E37	=IFERROR(SUMIFS('Vessel_Utilization Report'!$T$22:$T$600027,'Vessel_Utilization Report'!$D$22:$D$600027,Summary_Report_Utilization!E$11,'Vessel_Utilization Report'!$F$22:$F$600027,Summary_Report_Utilization!$D$6,'Vessel_Utilization Report'!$C$22:$C$600027,Summary_Report_Utilization!$B$11)/24,"")
E38:J38	E38	=IFERROR(SUMIFS('Vessel_Utilization Report'!$N$22:$N$600027,'Vessel_Utilization Report'!$D$22:$D$600027,Summary_Report_Utilization!E$11,'Vessel_Utilization Report'!$C$22:$C$600027,Summary_Report_Utilization!$B$11)/24,"")
E39:J39	E39	=IFERROR(SUM(E24:E38),"")
E40:J40	E40	=IFERROR(SUMIFS('Vessel_Utilization Report'!$R$22:$R$600027,'Vessel_Utilization Report'!$D$22:$D$600027,Summary_Report_Utilization!E$11,'Vessel_Utilization Report'!$F$22:$F$600027,Summary_Report_Utilization!$D$8,'Vessel_Utilization Report'!$C$22:$C$600027,Summary_Report_Utilization!$B$11)/24,"")
E42:J42	E42	=SUM(E40:E41)
E43:J43	E43	=IFERROR(SUMIFS('Vessel_Utilization Report'!$U$22:$U$60013,'Vessel_Utilization Report'!$D$22:$D$60013,Summary_Report_Utilization!E11,'Vessel_Utilization Report'!$F$22:$F$60013,Summary_Report_Utilization!$E$6,'Vessel_Utilization Report'!$C$22:$C$60013,Summary_Report_Utilization!$B$11)/24,"")
E44:J44	E44	=SUM(E22,E39,E42)
E46:J46	E46	=IFERROR(SUM(E22,E39,E42)/E44,"")
E48:J48	E48	=IFERROR(SUM(E22)/E44,"")