VBA & Macro Problems

Calculate Regular and Overtime Hours with Excel VBA

Muhammad TalhaMuhammad Talha Nov 22, 2026 868 views

Question details

Calculate regular and overtime hours by comparing an employee's scheduled and worked time intervals.

Calculate Regular and Overtime Hours with Excel VBA
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.
Before you start

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.

Solution 1Recommended

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.

1
Enter the times

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.

2
Add the macro

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

3
Run and check the results

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.

Use VBA to calculate the overlap and overtime
Scope of the calculation: This macro treats all worked time outside the scheduled interval as overtime. Overnight shifts, unpaid breaks, multiple intervals, and other overtime rules require additional logic.
Calculate hours without a macro

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. 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. 2. Calculate regular hours: In A5, enter =MAX(0,MIN(B2,D2)-MAX(A2,C2))*24 to calculate the overlapping hours.
  3. 3. Calculate overtime: In B5, enter =(D2-C2)*24-A5 to calculate worked hours outside the schedule.
  4. 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.
Calculate regular and overtime hours without VBAOpen and save Microsoft Excel XLSX filesAdjust the input times to recalculate the resultsUse a familiar spreadsheet interface
microsoft office alternative - wps office

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.