Calculating overtime hours


Overtime is paid after 45 hours.  I have managed to get my spreadsheet to calculate time with breaks included.  However when I try to subtract (45) from the Total Hours worked I get an error. Dates and times that are negative or two large show as ######.

Date Day In Out In Out Total Hours Overtime Hrs. 10/2/2019 Wednesday 6:00:00 AM 12:15:00 PM 1:00:00 PM 5:00:00 PM 10:15   10/3/2019 Thursday 6:00:00 AM     5:00:00 PM 11:00   10/4/2019 Friday 6:00:00 AM     5:00:00 PM 11:00   10/5/2019 Saturday             10/6/2019 Sunday             10/7/2019 Monday 6:00:00 AM     5:00:00 PM 11:00   10/8/2019 Tuesday 6:00:00 AM     5:00:00 PM 11:00  

Total Hours Worked

=SUM(D12-C12)+(F12-E12) to calculate the hours

=SUM(G19-45) for the overtime

          54:15:00 ###########


This worked perfectly!  You have to remember to format the cell under Number_Custom [h]:mm:ss.  Thank you!
kmartel7 Oct 17, '19 at 9:04 am
Dates are a great challenge in excel. It has built in cunning to see data entered in a cell and decide it is a date. It then stores it as 2 numbers with a decimal point between them. The first number is the number of days since the start date of its calendar the second number is the number of milliseconds since midnight. Hence when you add or subtract whole numbers you go forward or backwards in days. And yes you may need to manage the formatting. 
k1w1sm (rep: 197) Oct 17, '19 at 1:54 pm
Add to Discussion



try - =SUM(G19-(45/24))

You are substracting 45 days


Answer the Question

You must create an account to use the forum. Create an Account or Login