• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Displaying Time Difference with late & early tags


New Member
Hi, Is there a formula that can identify as late someone that is supposed to start at 11:00 pm, but instead starts at 12:00 am. I have used many formulas and I can't seem to find one that works for time that passes the 12:00 am. The formulas just give me an early by 22 hours instead of late by 1 hr. What I am trying to do (if at all possible) is.... I have the Appointment Time, the arrival time and the Departure time. The emp that waits for more than 2 hours at his destination gets paid for the time (above the 2 hrs) but if the employee is late at his destination for more than 1/2 hr, he doesn't get paid for any wait time, if he is early, he get paid for any time above the 2 hours from the appointment time (not the arrival). All time must be calculated in .25 increments. Here is my sheet with some of the formulas I used.



Excel Ninja
When calculating difference between 11:00 pm and 12:00 am without date value, difference is always less than 24 hours, and assuming that 2nd value is always later than first...

=MROUND(MOD(2nd time-1st time,1),1/24/4)

But, looking at your sample, 2nd value can be earlier or later than 1st value.
In this case, you can use Column1 as condition. But again, your sample is inconsistent in labeling in this column.

Alternate method is to enter time value using 24 hour clock.

ex: 12:00 am, if it's after 1st value, entered as 24:00 rather than 12:00 am/0:00.

My preference in this case, is to enforce datetime stamp on time tracking, rather than using time alone.
If that isn't feasible, you'll need to come up with logic/process to track at least 'late' consistently in column 1.