Calculate Regular and Overtime Hours with Excel VBA
Question details
Calculate regular and overtime hours by comparing an employee's scheduled and worked time intervals.

- Product
- Microsoft Excel VBA
- Device & OS
- not provided
- Scenario
- An employee is scheduled from 07:00 to 16:00 and works from 09:00 to 18:00.
- Observed behavior
- The desired result is 7 regular hours within the schedule and 2 overtime hours outside it.
Enter the scheduled start and end times in A2 and B2, and the worked start and end times in C2 and D2. The example below assumes valid Excel time values, a single same-day shift, and no unpaid breaks.
Use VBA to calculate the overlap and overtime
Use this macro to write decimal regular and overtime hours to A5 and B5.
Regular time is the overlap between the scheduled and worked intervals. Overtime is the remaining worked time outside the schedule.
On the worksheet, enter 07:00 in A2, 16:00 in B2, 09:00 in C2, and 18:00 in D2. Format the input cells as Time if needed.
Open the VBA editor, choose Insert > Module, and paste this code: Sub CalculateHours() Dim ws As Worksheet Dim c As Range Dim schedStart As Double, schedEnd As Double Dim workStart As Double, workEnd As Double Dim regularHours As Double, overtimeHours As Double Set ws = ActiveSheet For Each c In ws.Range("A2:D2") If Not IsNumeric(c.Value2) Or IsEmpty(c.Value2) Then MsgBox "Enter valid times in A2:D2." Exit Sub End If If c.Value2 < 0 Or c.Value2 >= 1 Then MsgBox "Use same-day time values without dates." Exit Sub End If Next c schedStart = ws.Range("A2").Value2 schedEnd = ws.Range("B2").Value2 workStart = ws.Range("C2").Value2 workEnd = ws.Range("D2").Value2 If schedEnd <= schedStart Or workEnd <= workStart Then MsgBox "Each end time must be later than its start time." Exit Sub End If regularHours = Application.Max(0, Application.Min(schedEnd, workEnd) - Application.Max(schedStart, workStart)) * 24 overtimeHours = (workEnd - workStart) * 24 - regularHours ws.Range("A5").Value = regularHours ws.Range("B5").Value = overtimeHours ws.Range("A5:B5").NumberFormat = "0.00" End Sub
Return to the worksheet, run CalculateHours from the Macros dialog, and check A5 for regular hours and B5 for overtime hours. The example returns 7.00 and 2.00.

Calculate Regular and Overtime Hours in WPS Spreadsheet
Use worksheet formulas in WPS Spreadsheet to compare a same-day schedule with worked hours and calculate the overlap and remaining time.
- 1. Enter the schedule: In WPS Spreadsheet, enter the scheduled start and end in A2 and B2, then the worked start and end in C2 and D2.
- 2. Calculate regular hours: In A5, enter =MAX(0,MIN(B2,D2)-MAX(A2,C2))*24 to calculate the overlapping hours.
- 3. Calculate overtime: In B5, enter =(D2-C2)*24-A5 to calculate worked hours outside the schedule.
- 4. Format and check: Format A5:B5 as Number. For 07:00–16:00 scheduled and 09:00–18:00 worked, check that the results are 7 and 2.

Frequently Asked Questions
Why do the calculations multiply by 24?
Excel and WPS Spreadsheet store times as fractions of a day. Multiplying the difference by 24 converts it to decimal hours.
What happens if the worked shift does not overlap the schedule?
The MAX function sets regular hours to zero, so all worked time is classified as overtime under this rule.
Can these calculations handle an overnight shift?
Not as written. The macro requires each end time to be later than its start time on the same day. Overnight shifts need date-and-time values or additional interval logic.
How do I account for an unpaid break?
Add logic to subtract the break's overlap with each relevant interval. Do not subtract the full break from regular hours unless it falls entirely within the regular worked interval.




