Help with Formula calculating time on 24H shifts

leoojeda 7 Posts Thursday December 21, 2017Registration date January 4, 2018 Last seen - Dec 26, 2017 at 04:31 AM - Latest reply: ac3mark 8634 Posts Monday June 3, 2013Registration dateModeratorStatus January 19, 2018 Last seen
- Jan 4, 2018 at 05:15 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.

I.E.
Clock in 18:00 Clock out 01:30

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?
See more 

5 replies

Reply to this topic
ac3mark 8634 Posts Monday June 3, 2013Registration dateModeratorStatus January 19, 2018 Last seen - Dec 26, 2017 at 05:05 PM
0
Helpful
1
OK, so you are using EXCEL for payroll procedures? Whew, not certain you can defend that one against the labor.

Check out this software only solution! When it comes to labor, you need an IRON clad solutions, or you will loose!
leoojeda 7 Posts Thursday December 21, 2017Registration date January 4, 2018 Last seen - Dec 29, 2017 at 01:33 AM
I am just building the schedule, payroll is done by the accounting dept. I just need a way to build their schedules and to show me whos going over hours and if they are getting a proper break
Respond to ac3mark
ac3mark 8634 Posts Monday June 3, 2013Registration dateModeratorStatus January 19, 2018 Last seen - Dec 29, 2017 at 11:25 AM
0
Helpful
2
OK, so calculate the 24 hour clock in a hidden column, and reformat another column to show the 12 hour clock for display. Simple fix!!



leoojeda 7 Posts Thursday December 21, 2017Registration date January 4, 2018 Last seen - Jan 4, 2018 at 08:47 AM
And how do I do that exactly?
ac3mark 8634 Posts Monday June 3, 2013Registration dateModeratorStatus January 19, 2018 Last seen - Jan 4, 2018 at 05:15 PM
make your 24 calculation in colum B. Hide colum B. In colum C convert the 24 hour clock into a 12 hour one using =TEXT(B1,"hh:mm:ss AM/PM")
Respond to ac3mark