Employee Tardiness based off start time

Hello Excel Gurus,

I cant seem to figure out how to track employee tardiness based off of the time they start. Basically I have employees who start at different times throughout the day in 15 minute intervals. They either start: on the hour,15 till,30 till, or 45 till. Now I need a formula that determines whether an employee is late based off their start time.

For example if employee "X" starts at 8:00 AM and clocks in at 8:01 AM. They will be considered late. If they are late then the column titled tardy will display a "Yes" or "No."

Thanks in advance,

See Attachment for sample

There is no data for the start time so I've based it as being 30 minutes before the minute clock time as early and 30 minutes after the minute clock time as late. i.e. for an employee starting at 8:00AM, 7:15AM will be shown as late.

=IF(AND(C2="On the Hour",MINUTE(E2)=0),"No",IF(AND(MINUTE(E2)<=IF(C2="On the Hour",59,IF(C2="15 Till",15,IF(C2="30 Till",30,45))),MINUTE(E2)>=IF(C2="On the Hour",30,IF(C2="15 till",45,IF(C2="30 till",0,15)))),"No","Yes"))

Originally Posted by yudlugar

There is no data for the start time so I've based it as being 30 minutes before the minute clock time as early and 30 minutes after the minute clock time as late. i.e. for an employee starting at 8:00AM, 7:15AM will be shown as late.

=IF(AND(C2="On the Hour",MINUTE(E2)=0),"No",IF(AND(MINUTE(E2)<=IF(C2="On the Hour",59,IF(C2="15 Till",15,IF(C2="30 Till",30,45))),MINUTE(E2)>=IF(C2="On the Hour",30,IF(C2="15 till",45,IF(C2="30 till",0,15)))),"No","Yes"))

Thank you yudlugar. However, I just found out that some employees clock in at different times so this will not work until I get some further info. For now, I ended up using the M Round function (And rounded the clock in time to the nearest 15 min interval). So if the clock in time is more that the rounded #, then they are considered late. Basically employees have a 7 min grace period before their clock in time. The following 7 min after the clock in time is the late period. And it repeats for the whole hour for every 15 min interval. Essentially, you end up with 4 grace periods and 4 late periods.

This works pretty good but there are some flaws. If someone clocks in a min before the grace period then they will be considered late. For example: If employee X is supposed to work at 10am, then they have from 9:53 till 10:00am to be considered on time. However, if they clocked in at 9:52 they will also be considered late. They will be flagged for clocking in too early though. It would be nice to be able to categorize the two but that seems too complex.