Formula to check number of days productive and non Productive

[Lukma]

Hi Friends

Am Having trouble with sum subtraction formula to get the numbers of days a vessel have works on my report

In Column C17 i Have all the Productive Time Sum In Days
In Column C34 i Have all the Non-Productive Time Sum In Days
In Column C37 I Have the both my Maintenance and Port crew Sum time in Days
In Column C38 I Have my Off-Hire

Now my Problem is In Column C39 for my Result and solution i need is to get the numbers of Days Each vessels have work in a month
IF C17 plus C34 = 30 days or 31 days then Subtract C37 and C38 to Get the Days Vessels Work in a Month in Column C39

That means i need to sum C17 and C34 and subtract C37 and C38 and my result should be in Column C39 for the numbers of Days vessel worked in a Full Month

Appreciate any help with the solution

Supply Vessels Utilization Reports 2021.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
7	Supply Vessels Productive Time	ADNOC-1010	ADNOC-1011	ADNOC-221	ADNOC-222	ADNOC-223	ADNOC-224	ADNOC-225	ADNOC-226	ADNOC-227	ADNOC-228	ADNOC-229	ADNOC-230	A-CHLOE	AMS-RUBY
8	Loading/ Offload ( Adnoc L&S Base )	4.3	2.0	5.3	3.7	5.5	4.7	0.9	1.4	2.0	1.2	4.2	3.8	3.3	3.4
9	Loading/ Offload (Free-Port)		0.1
10	Loading/ Offload Drilling / Artificial Islands	5.2	4.2	6.4	0.4	3.3	4.0	0.2		0.1	1.0	0.2		1.5
11	Loading/ Offload Jackup Rigs			3.8	15.0	5.3	6.3	23.5	2.6	19.2	16.7	18.2	12.9	6.5	6.3
12	Loading/ Offload Barges					0.1								1.0	1.2
13	Loading/ Offload ( Complexes )
14	Loading/ Offload Prd-Island (DAS / Arzanah/ ZIRKU)	0.1					0.0								0.0
15	Others / Marine/Rig Move
16	In Transit / Steaming to Location	12.8	12.4	9.9	6.6	9.3	8.7	4.0	2.4	8.3	8.6	5.3	9.2	10.2	10.9
17	Sub Total	22.4	18.7	25.5	25.8	23.5	23.8	28.6	6.4	29.6	27.5	27.9	25.9	22.5	21.8
18	Supply Vessel's Non-Productive Time
19	Waiting Drilling Supply Material	0.1													0.1
20	Waiting For Adnoc L&S Base Operations Readines	0.5	1.5		0.0	0.3	0.6	0.5	0.3	0.2	0.3	0.9	0.8	1.9	0.9
21	Waiting For Free-Port/Mus-Port Readines		1.0								0.1
22	Waiting On VTS Permission		0.3			0.1		0.8		0.1	0.0	0.7	0.7
23	Waiting On Channel Closure
24	Waiting Berthing Adnoc L&S	0.8	0.5	0.4	0.3	1.5	0.7			0.1	0.1			0.1	0.1
25	Waiting On DayLight Complexes
26	Waiting On Prd-Island (DAS / Arzanah/ ZIRKU)						1.2
27	Waiting Instruction Rigs/Island			1.8	1.1	1.8	0.9							1.8	2.9
28	Waiting On Jackup Rigs						0.1		2.6				2.5		0.4
29	Waiting on Barges & Field Vessels
30	Waiting On DayLight / Artificial Islands/ Prd-Island	1.3	1.8	1.1		2.2	1.8	0.2						0.5
31	Waiting Instruction Artifical Island	3.3	5.6			0.5	1.0				1.1			0.1
32	Waiting On Weather ( Wind Speed / Sea Condition )	1.6	0.5	1.3	0.0	0.3					0.8	0.3		0.2	3.4
33	Waiting on Weather (Fog / poor visibility)													0.1
34	Sub Total	7.6	11.2	4.6	1.4	6.5	6.2	1.5	2.9	0.4	2.5	2.0	4.1	4.7	7.8
35	Maintenance				2.8				20.6					2.8
36	Port Crew Change / Supply								0.1
37	Sub- TOTAL				2.8				20.8					2.8
38	Off-Hire														0.4
39	TOTAL Days	30	30	30	27	30	30	30	9	30	30	30	30	27	30
40					2.8				20.8					2.8	0.4
41	Vessel Availability	100%	100%	100%	100%	100%	100%	100%	100%	100%	100%	100%	100%	100%	100%
42					On Mnt's				On Mnt's					On Mnt's
43	Productive Time	75%	63%	85%	95%	78%	79%	95%	69%	99%	92%	93%	86%	83%	74%
Supply_Vessels_Utilization

Cell Formulas
Range	Formula
C38:P38,C35:P36,C8:P16	C8	=IFERROR(1/(1/SUMIFS(INDEX($BC$8:$BZ$1574,0,MATCH($B$6,$BC$7:$BZ$7,0)),$BA$8:$BA$1574,C$7,$BB$8:$BB$1574,$B8)),"")
C17:P17	C17	=IFERROR(1/(1/SUM(C8:C16)),"")
C19:P33	C19	=IFERROR(1/(1/SUMIFS(INDEX($BC$8:$BZ$1547,0,MATCH($B$6,$BC$7:$BZ$7,0)),$BA$8:$BA$1547,C$7,$BB$8:$BB$1547,$B19)),"")
C34:P34	C34	=IFERROR(1/(1/SUM(C19:C33)),"")
C37:P37	C37	=IFERROR(1/(1/SUM(C35:C36)),"")
C39:P39	C39	=SUM(C17,C34)
C40:P40	C40	=IFERROR(1/(1/SUM(C37:C38)),"")
C41:P41	C41	=IFERROR(SUM(C17,C34)/C39,"")
C42:P42	C42	=IF(C35="",C35,IF(C35>=0.1,"On Mnt's",IF(C35="","")))
C43:P43	C43	=IFERROR(SUM(C17)/C39,"")

 

[Dermot]

How about this? It adds C17 and C34, and only subtracts C37 and C38 if the total days is 30 or more
=C17+C34 -(C17+ C34>=30)*(C37+ C38)

 

[FMHasan]

IF C17 plus C34 = 30 days or 31 days , thats your desire condition, but what you want if this ans did not come? I mean if C17+ C34 <30, which result you want?