Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Calculating overtime hours

0

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 ###########
Answer
Discuss

Discussion

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

Answers

0

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

You are substracting 45 days

Discuss


Answer the Question

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