ISBLANK formula not working!!

hoyos

Board Regular
Joined
Jul 10, 2012
Messages
98
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,611
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jul 10, 2012
Messages
98
Office Version
  1. 365
Platform
  1. Windows
Thanks Jason I'm having problems downloading XL2BB or uploading the spreadsheet, is there another way of sending you the file?
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,611
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jul 10, 2012
Messages
98
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
ABCDEFGHIJKLMNOPQRS
1Matt WilcockDuty and Flying Hours Record SheetFrom21 Sep to 18 Oct 2022
2
3DUTY HOURSFLYING HOURSOFFHave I had 2 off consec in 14More then 4 lates in 7More then 3 consec lates34hrs off after late finish before early shift
4Day NoDayDateDuty Period28 Day Total7 Day TotalFlight Times28 Day Total7 Day Total14 Day TotalDuty / Remarks
5StartFinishHoursFirst T/OLast LdgFly Hrs
61Wed21-Sep18:0003:009:00171:0057:000:000:004433 HEMS Late
72Thu22-Sep171:0057:000:000:005533 OFF
83Fri23-Sep171:0047:000:000:006633 OFF
94Sat24-Sep161:0037:000:000:007733 OFF
105Sun25-Sep08:0018:0010:00161:0037:000:000:007733 HEMS Early
116Mon26-Sep08:0018:0010:00161:0038:000:000:006622 HEMS Early
127Tue27-Sep08:0018:0010:00162:0039:000:000:005511 HEMS Early
138Wed28-Sep18:0003:009:00162:0057:000:000:004411 HEMS Late
149Thu29-Sep18:0003:009:00162:0048:000:000:003322 HEMS Late
1510Fri30-Sep18:0003:009:00171:0057:000:000:003333YESHEMS Late
1611Sat1-Oct171:0057:000:000:004433 OFF
1712Sun2-Oct15:0018:003:00174:0050:000:000:004433NOHEMS Early
1813Mon3-Oct174:0040:000:000:005533 OFF
1914Tue4-Oct164:0030:000:000:006633 OFF
2015Wed5-Oct08:0018:0010:00164:0031:000:000:006622BLANK2HEMS Early
2116Thu6-Oct08:0018:0010:00164:0032:000:000:005511YESHEMS Early
2217Fri7-Oct08:0018:0010:00165:0033:000:000:004400YESHEMS Early
2318Sat8-Oct18:0003:009:00165:0042:000:000:003311YESHEMS Late
2419Sun9-Oct18:0003:009:00165:0048:000:000:003322BLANKHEMS Late
2520Mon10-Oct18:0003:009:00174:0057:000:000:003333NOHEMS Late
2621Tue11-Oct174:0057:000:000:004433 OFF
2722Wed12-Oct174:0047:000:000:005533 OFF
2823Thu13-Oct174:0037:000:000:006633 OFF
2924Fri14-Oct164:0027:000:000:007733 OFF
3025Sat15-Oct08:0018:0010:00164:0028:000:000:006622BLANK2HEMS Early
3126Sun16-Oct08:0018:0010:00164:0029:000:000:006611YESHEMS Early
3227Mon17-Oct08:0018:0010:00165:0030:000:000:005500BLANK2HEMS Early
3328Tue18-Oct18:0003:009:00165:0039:000:000:004411BLANK2HEMS Late
34Totals this period:Leave:0Sick:0Off:10
Matt Wilcock
Cell Formulas
RangeFormula
M1M1=C6
S1S1=" to " & TEXT(C33,"d mmm yyyy")
B6:B33B6=VLOOKUP(WEEKDAY(C6),$AE$6:$AF$12,2)
C6C6=AF16-(AF18*28)
C7:C33C7=C6+1
L6L6=SUM(K$6:K6,AI6:AI$33)
M6:M11M6=SUM(K$6:K6,AI28:AI$33)
L7:L14,L16:L20,L22:L32L7=SUM(K$6:K7,AI8:AI$33)
M12:M14,M16:M20,M22:M33,H12,H14:H33M12=SUM(K6:K12)
L15L15=SUM(K$6:K16,AI16:AI$33)
M15M15=SUM(K9:K16)
L21L21=SUM(K$6:K20,AI22:AI$33)
M21M21=SUM(K16:K20)
L33,G33L33=SUM(K6:K33)
O6:O18O6=SUM(AK$6:AK6,AO21:AO$33)
P6:Q11P6=SUM(AL$6:AL6,AP28:AP$33)
R6:R17R6=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:Q33P12=SUM(AL6:AL12)
R18:R33R18=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:O33O19=SUM(AK6:AK19)
G6G6=SUM(F$6:F6,AH6:AH$33)
G7:G32G7=SUM(F$6:F7,AH8:AH$33)
H6:H11H6=SUM(F$6:F6,AH28:AH$33)
H13H13=SUM(F$6:F13,AH$33:AH35)
H34H34=SUM(Z6:Z33)
K34K34=SUM(AA6:AA33)
N6:N18N6=SUM(AC$6:AC6,AJ21:AJ$33)
N19:N33N19=SUM(AC6:AC19)
N34N34=SUM(AB6:AB33)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
Q6:Q33Cell Value>3textNO
P6:P33Cell Value>4textNO
H6:H33Cell Value>2.5textYES
G6:G33Cell Value>8.33333333333333textYES
H6:H33Cell Value>2.5textYES
L6:L33Cell Value>90textYES
M6:M33Cell Value>30textYES
G6:G33Cell Value>200textYES
H6:H33Cell Value>60textYES


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

Board Regular
Joined
Jul 10, 2012
Messages
98
Office Version
  1. 365
Platform
  1. Windows
Here is a smaller version

FTL Matt Wilcock_New edition 10.xlsm
ABCDEFGHIJKLMNOPQRS
5StartFinishHoursFirst T/OLast LdgFly Hrs
61Wed21-Sep18:0003:009:00171:0057:000:000:004433 HEMS Late
72Thu22-Sep171:0057:000:000:005533 OFF
83Fri23-Sep171:0047:000:000:006633 OFF
94Sat24-Sep161:0037:000:000:007733 OFF
105Sun25-Sep08:0018:0010:00161:0037:000:000:007733 HEMS Early
116Mon26-Sep08:0018:0010:00161:0038:000:000:006622 HEMS Early
127Tue27-Sep08:0018:0010:00162:0039:000:000:005511 HEMS Early
138Wed28-Sep18:0003:009:00162:0057:000:000:004411 HEMS Late
149Thu29-Sep18:0003:009:00162:0048:000:000:003322 HEMS Late
1510Fri30-Sep18:0003:009:00171:0057:000:000:003333YESHEMS Late
1611Sat1-Oct171:0057:000:000:004433 OFF
1712Sun2-Oct15:0018:003:00174:0050:000:000:004433NOHEMS Early
1813Mon3-Oct174:0040:000:000:005533 OFF
1914Tue4-Oct164:0030:000:000:006633 OFF
Matt Wilcock
Cell Formulas
RangeFormula
B6:B19B6=VLOOKUP(WEEKDAY(C6),$AE$6:$AF$12,2)
C6C6=AF16-(AF18*28)
C7:C19C7=C6+1
G6G6=SUM(F$6:F6,AH6:AH$33)
H6:H11H6=SUM(F$6:F6,AH28:AH$33)
G7:G19G7=SUM(F$6:F7,AH8:AH$33)
H12,H14:H19,M12:M14,M16:M19H12=SUM(F6:F12)
H13H13=SUM(F$6:F13,AH$33:AH35)
L6L6=SUM(K$6:K6,AI6:AI$33)
M6:M11M6=SUM(K$6:K6,AI28:AI$33)
N6:N18N6=SUM(AC$6:AC6,AJ21:AJ$33)
O6:O18O6=SUM(AK$6:AK6,AO21:AO$33)
P6:Q11P6=SUM(AL$6:AL6,AP28:AP$33)
R6:R17R6=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:L19L7=SUM(K$6:K7,AI8:AI$33)
P12:Q19P12=SUM(AL6:AL12)
L15L15=SUM(K$6:K16,AI16:AI$33)
M15M15=SUM(K9:K16)
R18:R19R18=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"))))
N19N19=SUM(AC6:AC19)
O19O19=SUM(AK6:AK19)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
Q6:Q33Cell Value>3textNO
P6:P33Cell Value>4textNO
H6:H33Cell Value>2.5textYES
G6:G33Cell Value>8.33333333333333textYES
H6:H33Cell Value>2.5textYES
L6:L33Cell Value>90textYES
M6:M33Cell Value>30textYES
G6:G33Cell Value>200textYES
H6:H33Cell Value>60textYES
 

hoyos

Board Regular
Joined
Jul 10, 2012
Messages
98
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,611
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jul 10, 2012
Messages
98
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Dec 30, 2008
Messages
14,611
Office Version
  1. 365
Platform
  1. Windows
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,972
Messages
5,767,400
Members
425,410
Latest member
SmittyT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top