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
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