# Formula for calculating work hours on overnight shifts

Closed
leoojeda Posts 7 Registration date Thursday December 21, 2017 Status Member Last seen January 4, 2018 - Dec 22, 2017 at 10:24 PM
Hello CCM community!, Here I am again with a question that's being bugging me for a while

A while ago I created my own version of a work scheduler that will calculate hours worked and also would take breaks off the worked hours depending on the length of the shifts.
This was all good for regular shifts but when we started having overnight shifts I couldn't just calculate the difference between punch out and punch in times.

The spreadsheet had 2 cells per day, clock in and clock out per day, and at the end of the week I had this:

A column were the names of employees,
B-C were Monday, D-E were Tuesday and so on until you reach P which had this formula:

=(C4-B4)+(E4-D4)+(G4-F4)+(I4-H4)+(K4-J4)+(M4-L4)+(O4-N4)

However, it was easy to calculate that using a 24H format, but when the shift ends past midnight I can't put 01:00 - 23:00 without having a negative number therefore the time totals won't be accurate.

Here are my questions:

How can I edit the second cell to accept that deleting a clock out time after midnight will calculate the time worked?
My worked around was to add 24 to the end time, I.E. 26:30 was 02:30am But when printing the schedule, the clock out times are not real life time and will cause confusion on whoever reads the schedule.

Any pointers on how to fix this?

Thanks again!