The formula of calculating overtime and late time with Excel I go to work at 8 a.m. and I'm late if I'm over 8 a.m =Text (mod (c2,1) + 0.625 - (weekday (c2,2) > 5) / 48, "[1.125]; late" I go to work at 8:00 in the morning. I'm late when I'm over 8:00 in the morning. I'm late when I'm over 13:30 in the afternoon. How can I use the formula to calculate the time in minutes? Because of the heavy workload, I want to ask the experts to give a formula to avoid repeated labor and improve work efficiency Name registration number date time machine number comparison method late late time overtime Lola 143 2010-12-1 8:00 1 fingerprint Lola 143 2010-12-1 12:19 1 fingerprint Lola 143 2010-12-1 12:21 1 fingerprint Lola 143 2010-12-1 13:29 1 fingerprint Lola 143 2010-12-1 17:51 1 fingerprint Lola 143 2010-12-2 12:04 1 fingerprint Lola 143 2010-12-2 13:29 1 fingerprint Lola 143 2010-12-2 17:42 1 fingerprint Lola 143 2010-12-2 23:52 1 fingerprint Lola 143 2010-12-3 8:03 1 fingerprint Lola 143 2010-12-3 12:03 1 fingerprint Lola 143 12 / 3 / 2010 13:30 1 fingerprint Lola 143 2010-12-3 17:48 1 fingerprint Lola 143 2010-12-3 19:59 1 fingerprint Lola 143 2010-12-3 22:10 1 fingerprint The perfect solution is plus points Your answer is very good, but I would like to ask: how is 0.666 in text (mod (c2,1) + 0.666 calculated

The formula of calculating overtime and late time with Excel I go to work at 8 a.m. and I'm late if I'm over 8 a.m =Text (mod (c2,1) + 0.625 - (weekday (c2,2) > 5) / 48, "[1.125]; late" I go to work at 8:00 in the morning. I'm late when I'm over 8:00 in the morning. I'm late when I'm over 13:30 in the afternoon. How can I use the formula to calculate the time in minutes? Because of the heavy workload, I want to ask the experts to give a formula to avoid repeated labor and improve work efficiency Name registration number date time machine number comparison method late late time overtime Lola 143 2010-12-1 8:00 1 fingerprint Lola 143 2010-12-1 12:19 1 fingerprint Lola 143 2010-12-1 12:21 1 fingerprint Lola 143 2010-12-1 13:29 1 fingerprint Lola 143 2010-12-1 17:51 1 fingerprint Lola 143 2010-12-2 12:04 1 fingerprint Lola 143 2010-12-2 13:29 1 fingerprint Lola 143 2010-12-2 17:42 1 fingerprint Lola 143 2010-12-2 23:52 1 fingerprint Lola 143 2010-12-3 8:03 1 fingerprint Lola 143 2010-12-3 12:03 1 fingerprint Lola 143 12 / 3 / 2010 13:30 1 fingerprint Lola 143 2010-12-3 17:48 1 fingerprint Lola 143 2010-12-3 19:59 1 fingerprint Lola 143 2010-12-3 22:10 1 fingerprint The perfect solution is plus points Your answer is very good, but I would like to ask: how is 0.666 in text (mod (c2,1) + 0.666 calculated

You can modify the formula as follows:
=If (mod (c2,1) 5) / 48, "[1] late"), text (mod (c2,1) + 0.458 - (weekday (c2,2) > 5) / 48, "[1] late"))
It is assumed that we will not go to work on Saturdays and Sundays
Weekday (c2,2) > 6 if working on Saturday
If you work on Saturdays and Sundays, delete "(weekday (c2,2) > 5) / 48"
Whether the time is before 12:00: if (mod (c2,1) 5) / 48, "[1] late"),
After 12 o'clock, late at 13 o'clock is considered late: text (mod (c2,1) + 0.458 - (weekday (c2,2) > 5) / 48, "[1] late")
If it is 11:30 instead of 12:00, then if (mod (c2,1)