Attendance Late Punch In / Out Highlighting

**MVRincon** · 11-08-2016, 01:02 PM

Good Morning,

I've searched the forums but haven't been able to quite find what I am looking for.

I am attempting to determine if people are clocking In / Out on time. I would like the entire row to be highlighted in addition mark "Yes" or "No". Preference is to use Formulas and/or Conditional formatting to achieve this functionality.

Someone is "on time" if they punch in between either of these two(2) time windows:
5:53 - 6:07
1:53 - 2:07

Data matrix look:

Column A Column B Column C Column D
Employee Time In Time Out Late?
Employee 1 05:53 PM 05:59 AM Y or N
Employee 2 05:53 PM 06:00 AM
Employee 3 06:00 PM 02:00 AM
Employee 4 05:53 PM 06:00 AM
Employee 5 05:55 PM 06:00 AM
Employee 6 08:52 PM 06:00 AM
Employee 7 05:55 PM 06:00 AM
Employee 8 05:53 PM 06:00 AM
Employee 9 05:53 PM 06:00 AM
Employee 10 01:00 PM 09:00 PM
Employee 11 01:02 PM 09:00 PM
Employee 12 06:59 AM 01:07 PM
Employee 13 06:56 AM 02:12 PM
Employee 14 02:00 PM 10:00 PM
Employee 15 01:02 PM 09:00 PM
Employee 16 01:01 PM 09:00 PM
Employee 17 01:06 PM 09:00 PM
Employee 18 01:01 PM 09:00 PM

Thank you!

**Bernie Deitrick** · 11-08-2016, 02:48 PM

You have seven minute window on either side of 6AM and 2PM - at least, that is my interpretation - for the arrivals. If those two time are different, change them in the formula.

Anyway, in D2, use something like this formula for arrival:

=IF(MIN(ABS(B2-"2:00:00 PM"),ABS(B2-"6:00:00 AM"))<=TIMEVALUE("0:07:00"),"On time","Late")

I was not sure if Y means on time or late, so I used words. You can change it to whatever you like. Copy down to match your list.

For the conditional fomatting, select A2:DXXX, choose CF, use the formula option, with the Formula

=$D2="Late" (or whatever you used to replace "Late" in my formula)

and choose the red fill.