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

 

[jasonb75]

As you are exceeding 24 hours you will need to have dates as well as times in your sheet.

We're going to need a visual example of your sheet in order to come up with anything that will do what you need, a description like that is open to a lot of misinterpretation.

You can use the XL2BB add-in (<<link) to attach a mini sheet to the forum (10-20 rows of data, not the whole sheet! Only the columns that are required for the formula, so A:E based on your formula. Create the example on a new sheet if necessary and include the expected output of the formula in column F.

Also, please update your account details (<<link) to show the version of excel that you are using so that we know which functions you can use.

 

[hoyos]

Thanks Jason I'm having problems downloading XL2BB or uploading the spreadsheet, is there another way of sending you the file?

 

[jasonb75]

You can use a file share site like onedrive and post a link to the file here, preferably one that does not require people to sign in before they can download your file.

 

[hoyos]

Hi, I think I have manage to use XL2Bb

You can use a file share site like onedrive and post a link to the file here, preferably one that does not require people to sign in before they can download your file.

I think I managed!!

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
1	Matt Wilcock				Duty and Flying Hours Record Sheet							From	21 Sep						to 18 Oct 2022
2
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	YES	HEMS Late
16	11	Sat	1-Oct				171:00	57:00				0:00	0:00	4	4	3	3		OFF
17	12	Sun	2-Oct	15:00	18:00	3:00	174:00	50:00				0:00	0:00	4	4	3	3	NO	HEMS Early
18	13	Mon	3-Oct				174:00	40:00				0:00	0:00	5	5	3	3		OFF
19	14	Tue	4-Oct				164:00	30:00				0:00	0:00	6	6	3	3		OFF
20	15	Wed	5-Oct	08:00	18:00	10:00	164:00	31:00				0:00	0:00	6	6	2	2	BLANK2	HEMS Early
21	16	Thu	6-Oct	08:00	18:00	10:00	164:00	32:00				0:00	0:00	5	5	1	1	YES	HEMS Early
22	17	Fri	7-Oct	08:00	18:00	10:00	165:00	33:00				0:00	0:00	4	4	0	0	YES	HEMS Early
23	18	Sat	8-Oct	18:00	03:00	9:00	165:00	42:00				0:00	0:00	3	3	1	1	YES	HEMS Late
24	19	Sun	9-Oct	18:00	03:00	9:00	165:00	48:00				0:00	0:00	3	3	2	2	BLANK	HEMS Late
25	20	Mon	10-Oct	18:00	03:00	9:00	174:00	57:00				0:00	0:00	3	3	3	3	NO	HEMS Late
26	21	Tue	11-Oct				174:00	57:00				0:00	0:00	4	4	3	3		OFF
27	22	Wed	12-Oct				174:00	47:00				0:00	0:00	5	5	3	3		OFF
28	23	Thu	13-Oct				174:00	37:00				0:00	0:00	6	6	3	3		OFF
29	24	Fri	14-Oct				164:00	27:00				0:00	0:00	7	7	3	3		OFF
30	25	Sat	15-Oct	08:00	18:00	10:00	164:00	28:00				0:00	0:00	6	6	2	2	BLANK2	HEMS Early
31	26	Sun	16-Oct	08:00	18:00	10:00	164:00	29:00				0:00	0:00	6	6	1	1	YES	HEMS Early
32	27	Mon	17-Oct	08:00	18:00	10:00	165:00	30:00				0:00	0:00	5	5	0	0	BLANK2	HEMS Early
33	28	Tue	18-Oct	18:00	03:00	9:00	165:00	39:00				0:00	0:00	4	4	1	1	BLANK2	HEMS Late
34	Totals this period:						Leave:	0		Sick:	0		Off:	10
Matt Wilcock

Cell Formulas
Range	Formula
M1	M1	=C6
S1	S1	=" to " & TEXT(C33,"d mmm yyyy")
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:R17	R6	=IF(ISBLANK(D6)=TRUE,"",IF(D4<>$AS$6,(IF(IF(AND(D2=$AS$6,D3=$AS$6,D4=$AS$6),1,0)=1,"BLANK",IF(AND(D2=$AS$6,D3=$AS$6,D4=$AS$6),IF(D6<=E4+TIME(10,0,0),"NO","OK"),""))),(IF(IF(AND(D2=$AS$6,D3=$AS$6,D4=$AS$6),1,0)=1,"NO","YES"))))
P12:Q33	P12	=SUM(AL6:AL12)
R18:R33	R18	=IF(ISBLANK(D18)=TRUE,"",IF(D20<>$AS$6,(IF(IF(AND(D17=$AS$6,D18=$AS$6,D19=$AS$6),1,0)=1,"BLANK",IF(AND(D16=$AS$6,D17=$AS$6,D18=$AS$6),IF(D20<=E18+TIME(10,0,0),"NO","SURE"),"BLANK2"))),(IF(IF(AND(D17=$AS$6,D18=$AS$6,D19=$AS$6),1,0)=1,"NO","YES"))))
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

You can use a file share site like onedrive and post a link to the file here, preferably one that does not require people to sign in before they can download your file.

 

[hoyos]

Here is a smaller version

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
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	YES	HEMS Late
16	11	Sat	1-Oct				171:00	57:00				0:00	0:00	4	4	3	3		OFF
17	12	Sun	2-Oct	15:00	18:00	3:00	174:00	50:00				0:00	0:00	4	4	3	3	NO	HEMS Early
18	13	Mon	3-Oct				174:00	40:00				0:00	0:00	5	5	3	3		OFF
19	14	Tue	4-Oct				164:00	30:00				0:00	0:00	6	6	3	3		OFF
Matt Wilcock

Cell Formulas
Range	Formula
B6:B19	B6	=VLOOKUP(WEEKDAY(C6),$AE$6:$AF$12,2)
C6	C6	=AF16-(AF18*28)
C7:C19	C7	=C6+1
G6	G6	=SUM(F$6:F6,AH6:AH$33)
H6:H11	H6	=SUM(F$6:F6,AH28:AH$33)
G7:G19	G7	=SUM(F$6:F7,AH8:AH$33)
H12,H14:H19,M12:M14,M16:M19	H12	=SUM(F6:F12)
H13	H13	=SUM(F$6:F13,AH$33:AH35)
L6	L6	=SUM(K$6:K6,AI6:AI$33)
M6:M11	M6	=SUM(K$6:K6,AI28:AI$33)
N6:N18	N6	=SUM(AC$6:AC6,AJ21:AJ$33)
O6:O18	O6	=SUM(AK$6:AK6,AO21:AO$33)
P6:Q11	P6	=SUM(AL$6:AL6,AP28:AP$33)
R6:R17	R6	=IF(ISBLANK(D6)=TRUE,"",IF(D4<>$AS$6,(IF(IF(AND(D2=$AS$6,D3=$AS$6,D4=$AS$6),1,0)=1,"BLANK",IF(AND(D2=$AS$6,D3=$AS$6,D4=$AS$6),IF(D6<=E4+TIME(10,0,0),"NO","OK"),""))),(IF(IF(AND(D2=$AS$6,D3=$AS$6,D4=$AS$6),1,0)=1,"NO","YES"))))
L7:L14,L16:L19	L7	=SUM(K$6:K7,AI8:AI$33)
P12:Q19	P12	=SUM(AL6:AL12)
L15	L15	=SUM(K$6:K16,AI16:AI$33)
M15	M15	=SUM(K9:K16)
R18:R19	R18	=IF(ISBLANK(D18)=TRUE,"",IF(D20<>$AS$6,(IF(IF(AND(D17=$AS$6,D18=$AS$6,D19=$AS$6),1,0)=1,"BLANK",IF(AND(D16=$AS$6,D17=$AS$6,D18=$AS$6),IF(D20<=E18+TIME(10,0,0),"NO","SURE"),"BLANK2"))),(IF(IF(AND(D17=$AS$6,D18=$AS$6,D19=$AS$6),1,0)=1,"NO","YES"))))
N19	N19	=SUM(AC6:AC19)
O19	O19	=SUM(AK6:AK19)

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

 

[hoyos]

Jason, from my original post I have replaced "Sheet1!$A$2" with $AS$6

 

[jasonb75]

That is all well and good but $A$6 contains a value of 1 which doesn't appear to be comparable to anything else that the formula refers to. The formula uses D2, D3, D4, and E4, none of which are in the smaller sheet.

Looking at the larger sheet, none of the cells in the formula appear to contain anything that can be compared to anything else. Gonna need a bit of an explanation as to what they all refer to before I can do anything.

 

[hoyos]

Sorry my mistake, it should read $AS$6 which is populated with the time 18:00:00.
The formula should look at col D and pick out three consecutive duties starting at 18:00hrs. It then looks at the last duty finish time in col E, there should be at least a minimum 34 hours off duty. If the user inserts a start time (col D) within the 34 hours a warning in col R same row should flag up with a red background with the word “NO” in black.
I hope that makes sense.

 

[jasonb75]

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.