Attendance After Midnight

[fiberboysa]

Hi All,
I am trying to make a Conditional Formation rule to highlight employees who left early then the schedule time out. But due to Midnight, I am stuck.
The scheduled timing is from 04:00 PM to 12:00 AM (Midnight). Following is attendance of an employee...

[TABLE="class: grid, width: 200, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Time In[/TD]
[TD]Time Out[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]03:50 PM[/TD]
[TD]12:10 AM[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]03:55 PM[/TD]
[TD]11:50 PM[/TD]
[/TR]
</tbody>[/TABLE]

Here time out in cell B2 should not be highlighted because employee left after scheduled time. But time out in cell B3 should be highlighted because he left 10 mins earlier. I can not figure it out because of change of date after midnight. Kindly Help!!! Thanks in advance...!

 

[JoeMo]

Use this conditional formatting rule for the 4:00 to midnight shift:

=MOD($B2-2/3,1)*24>=8

 

[fiberboysa]

Use this conditional formatting rule for the 4:00 to midnight shift:

=MOD($B2-2/3,1)*24>=8

Its giving me "FALSE" if i apply it to cell B3. Means 11:50 PM should be highlighted because employee left 10 mins earlier. With FALSE it wont be highlighted.

 

[JoeMo]

Its giving me "FALSE" if i apply it to cell B3. Means 11:50 PM should be highlighted because employee left 10 mins earlier. With FALSE it wont be highlighted.

Sorry, there's an obvious typo in there. Should be:

=MOD($B2-2/3,1)*24<=8

 

[fiberboysa]

Sorry, there's an obvious typo in there. Should be:

=MOD($B2-2/3,1)*24<=8

Thanks dear