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