ISBLANK formula not working!!

[hoyos]

I’m trying to get the formula below to work but having no luck. It should flag up if after a run of three night shift (18:00pm to 03:00am) if I enter a start time for a new shift anytime under 34 hours after the end of the last night shift.

R11
if( ISBLANK(D11) = TRUE,"",IF(D13<>Sheet1!$A$2,(IF(IF(AND(D10=Sheet1!$A$2,D11=Sheet1!$A$2,D12=Sheet1!$A$2),1,0) = 1,"Blank",IF(AND(D9=Sheet1!$A$2,D10=Sheet1!$A$2,D11=Sheet1!$A$2),IF(D13 <= E11 + TIME(34,0,0),"No","Sure"),"Blank2"))),(IF(IF(AND(D10=Sheet1!$A$2,D11=Sheet1!$A$2,D12=Sheet1!$A$2),1,0) = 1,"No","Yes"))))

Sheet1!$A$2 is populated with the shift start time of 18:00 as a reference.
Column D= start time of shift
Column C= end time of shift
Column R= formula

Any help would be appreciated

 

[hoyos]

What do you have in AF16 and AF18?

There is other missing data but I don't think that it will affect the formulas that are needed for the question.

AF16 is starting date and AF18 is the number of the period.
Each period has 28 days and over one year there are 13 periods. There is a couple of buttons which starts a new period or backwards to view previous periods. They shouldn't affect the formulas.

 

[jasonb75]

Try this in R6 and fill down.

=IFERROR(IF(D6="","",IF(COUNTIF(D2:D5,0.75)=3,IF(SUM(C6:D6,-INDEX(C2:E5,MATCH(1E+100,D2:D5),{1,3}))<--"34:00:00","NO","YES"),"")),"")

 

[hoyos]

Try this in R6 and fill down.

=IFERROR(IF(D6="","",IF(COUNTIF(D2:D5,0.75)=3,IF(SUM(C6:D6,-INDEX(C2:E5,MATCH(1E+100,D2:D5),{1,3}))<--"34:00:00","NO","YES"),"")),"")

Hi Jason, doesn’t quite working! Need to account if the row above is blank. ie if you look at row 17 it’s showing “yes” until I populate row 16 then it indicates “no”. After a run of three night duties you will always have a blank row. Hope that helps.

 

[jasonb75]

if you look at row 17 it’s showing “yes”

Why is that wrong? You gave a very unclear explanation, it was my interpretation that it should be "NO" if it was less than 34 hours, or "YES" if it was more than 34 hours.
With the example you give above, there is a difference of 36 hours.

 

[hoyos]

Why is that wrong? You gave a very unclear explanation, it was my interpretation that it should be "NO" if it was less than 34 hours, or "YES" if it was more than 34 hours.
With the example you give above, there is a difference of 36 hours.

I apologise for the confusion, having finished on the third night I must have at least 34 hours free of duty from what ever time I finish on the third night. For example:
If I finish at 03:00am on the 15th I can’t start a shift till 13:00pm on the 17th.
Or if I finish at 05:00am on the 15th I can’t start a new shift till 15:00pm on the 17th.

 

[jasonb75]

You still haven't answered my question. Why is "Yes" wrong on the row that you queried? There is a gap of more than 34 hours so as far as I can see, the result shown is correct.

 

[hoyos]

If you look at row 17 it's indicating "yes" where it should show "no" as the start time is well before the 34 hours allowed.

FTL Matt Wilcock_New edition 10.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
3				DUTY HOURS					FLYING HOURS					OFF	Have I had 2 off consec in 14	More then 4 lates in 7	More then 3 consec lates	34hrs off after late finish before early shift
4	Day No	Day	Date	Duty Period			28 Day Total	7 Day Total	Flight Times			28 Day Total	7 Day Total	14 Day Total					Duty / Remarks
5				Start	Finish	Hours			First T/O	Last Ldg	Fly Hrs
6	1	Wed	21-Sep	18:00	03:00	9:00	171:00	57:00				0:00	0:00	4	4	3	3		HEMS Late
7	2	Thu	22-Sep				171:00	57:00				0:00	0:00	5	5	3	3		OFF
8	3	Fri	23-Sep				171:00	47:00				0:00	0:00	6	6	3	3		OFF
9	4	Sat	24-Sep				161:00	37:00				0:00	0:00	7	7	3	3		OFF
10	5	Sun	25-Sep	08:00	18:00	10:00	161:00	37:00				0:00	0:00	7	7	3	3		HEMS Early
11	6	Mon	26-Sep	08:00	18:00	10:00	161:00	38:00				0:00	0:00	6	6	2	2		HEMS Early
12	7	Tue	27-Sep	08:00	18:00	10:00	162:00	39:00				0:00	0:00	5	5	1	1		HEMS Early
13	8	Wed	28-Sep	18:00	03:00	9:00	162:00	57:00				0:00	0:00	4	4	1	1		HEMS Late
14	9	Thu	29-Sep	18:00	03:00	9:00	162:00	48:00				0:00	0:00	3	3	2	2		HEMS Late
15	10	Fri	30-Sep	18:00	03:00	9:00	171:00	57:00				0:00	0:00	3	3	3	3		HEMS Late
16	11	Sat	1-Oct				171:00	57:00				0:00	0:00	3	3	3	3
17	12	Sun	2-Oct	08:00	18:00	10:00	181:00	57:00				0:00	0:00	3	3	3	3	YES	HEMS Early
18	13	Mon	3-Oct				181:00	47:00				0:00	0:00	4	4	3	3		OFF
19	14	Tue	4-Oct				171:00	37:00				0:00	0:00	5	5	3	3		OFF
20	15	Wed	5-Oct	08:00	18:00	10:00	171:00	38:00				0:00	0:00	5	5	2	2		HEMS Early
21	16	Thu	6-Oct	08:00	18:00	10:00	171:00	39:00				0:00	0:00	4	4	1	1		HEMS Early
22	17	Fri	7-Oct	08:00	18:00	10:00	172:00	40:00				0:00	0:00	3	3	0	0		HEMS Early
23	18	Sat	8-Oct	18:00	03:00	9:00	172:00	49:00				0:00	0:00	2	2	1	1		HEMS Late
24	19	Sun	9-Oct	18:00	03:00	9:00	172:00	48:00				0:00	0:00	2	2	2	2		HEMS Late
25	20	Mon	10-Oct	18:00	03:00	9:00	181:00	57:00				0:00	0:00	2	2	3	3		HEMS Late
26	21	Tue	11-Oct				181:00	57:00				0:00	0:00	3	3	3	3		OFF
27	22	Wed	12-Oct				181:00	47:00				0:00	0:00	4	4	3	3		OFF
28	23	Thu	13-Oct				181:00	37:00				0:00	0:00	5	5	3	3		OFF
29	24	Fri	14-Oct				171:00	27:00				0:00	0:00	6	6	3	3		OFF
30	25	Sat	15-Oct	08:00	18:00	10:00	171:00	28:00				0:00	0:00	6	6	2	2		HEMS Early
31	26	Sun	16-Oct	08:00	18:00	10:00	171:00	29:00				0:00	0:00	6	6	1	1		HEMS Early
32	27	Mon	17-Oct	08:00	18:00	10:00	172:00	30:00				0:00	0:00	5	5	0	0		HEMS Early
33	28	Tue	18-Oct	18:00	03:00	9:00	172:00	39:00				0:00	0:00	4	4	1	1		HEMS Late
34	Totals this period:						Leave:	0		Sick:	0		Off:	9
Matt Wilcock

Cell Formulas
Range	Formula
B6:B33	B6	=VLOOKUP(WEEKDAY(C6),$AE$6:$AF$12,2)
C6	C6	=AF16-(AF18*28)
C7:C33	C7	=C6+1
L6	L6	=SUM(K$6:K6,AI6:AI$33)
M6:M11	M6	=SUM(K$6:K6,AI28:AI$33)
L7:L14,L16:L20,L22:L32	L7	=SUM(K$6:K7,AI8:AI$33)
M12:M14,M16:M20,M22:M33,H12,H14:H33	M12	=SUM(K6:K12)
L15	L15	=SUM(K$6:K16,AI16:AI$33)
M15	M15	=SUM(K9:K16)
L21	L21	=SUM(K$6:K20,AI22:AI$33)
M21	M21	=SUM(K16:K20)
L33,G33	L33	=SUM(K6:K33)
O6:O18	O6	=SUM(AK$6:AK6,AO21:AO$33)
P6:Q11	P6	=SUM(AL$6:AL6,AP28:AP$33)
R6:R33	R6	=IFERROR(IF(D6="","",IF(COUNTIF(D2:D5,0.75)=3,IF(SUM(C6:D6,-INDEX(C2:E5,MATCH(1E+100,D2:D5),{1,3}))<--"34:00:00","NO","YES"),"")),"")
P12:Q33	P12	=SUM(AL6:AL12)
O19:O33	O19	=SUM(AK6:AK19)
G6	G6	=SUM(F$6:F6,AH6:AH$33)
G7:G32	G7	=SUM(F$6:F7,AH8:AH$33)
H6:H11	H6	=SUM(F$6:F6,AH28:AH$33)
H13	H13	=SUM(F$6:F13,AH$33:AH35)
H34	H34	=SUM(Z6:Z33)
K34	K34	=SUM(AA6:AA33)
N6:N18	N6	=SUM(AC$6:AC6,AJ21:AJ$33)
N19:N33	N19	=SUM(AC6:AC19)
N34	N34	=SUM(AB6:AB33)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
Q6:Q33	Cell Value	>3	text	NO
P6:P33	Cell Value	>4	text	NO
H6:H33	Cell Value	>2.5	text	YES
G6:G33	Cell Value	>8.33333333333333	text	YES
H6:H33	Cell Value	>2.5	text	YES
L6:L33	Cell Value	>90	text	YES
M6:M33	Cell Value	>30	text	YES
G6:G33	Cell Value	>200	text	YES
H6:H33	Cell Value	>60	text	YES

 

[jasonb75]

If you look at row 17 it's indicating "yes" where it should show "no" as the start time is well before the 34 hours allowed.

We are comparing row 17 to row 15 yes?

That is a difference of 53 hours!!!

edit:- ignore me, I just realised that the finish time is the following day. I'll do a quick edit to the formula and post it shortly.

If the finish time for an 18:00 start will always be after midnight then the easiest way to fix it with your sheet layout would be to change "34:00:00" to "58:00:00", i.e. adding another 24 hours to it.

 

[hoyos]

I’ll try that and get back to. Thanks

 

[hoyos]

We are comparing row 17 to row 15 yes?

That is a difference of 53 hours!!!

edit:- ignore me, I just realised that the finish time is the following day. I'll do a quick edit to the formula and post it shortly.

If the finish time for an 18:00 start will always be after midnight then the easiest way to fix it with your sheet layout would be to change "34:00:00" to "58:00:00", i.e. adding another 24 hours to it.

Hi Jason, Yes that works. Many thanks for your patience with me on this task much appreciated.