Date & Time
Calculate hours that overlap a specific time window (shift differential)
Measure exact hours of a work shift or booking that fall inside an evening differential or peak-rate time block using MAX and MIN.
Interval intersection math for shift differentials
Hospitals, warehouses, and utilities often pay an evening differential for hours worked between 18:00 and 22:00. A shift from 14:00 to 22:30 overlaps that window from 18:00 to 22:00 (4.00 hours).
Two same-day intervals [ShiftStart, ShiftEnd] and [WinStart, WinEnd] intersect from MAX(ShiftStart, WinStart) to MIN(ShiftEnd, WinEnd). Wrapping the difference in MAX(0, ...) clamps non-overlapping morning shifts cleanly to 0.
| ShiftStart (A) | ShiftEnd (B) | WinStart (C) | WinEnd (D) | Overlap Hours |
|---|---|---|---|---|
| 14:00 | 22:30 | 18:00 | 22:00 | 4.00 hrs (18:00–22:00) |
| 19:30 | 21:00 | 18:00 | 22:00 | 1.50 hrs (19:30–21:00) |
| 08:00 | 16:00 | 18:00 | 22:00 | 0.00 hrs |
Same-day time window overlap formula
Multiply the fractional day overlap by 24 and round to 2 decimal places for payroll multiplication.
=ROUND(MAX(0, MIN(B2, D2) - MAX(A2, C2)) * 24, 2)Regular non-differential hours in the same shift
Subtract window overlap hours from total shift hours (B2 - A2) * 24.
=ROUND(((B2 - A2) - MAX(0, MIN(B2, D2) - MAX(A2, C2))) * 24, 2)Inputs and interpretation
The time-only example requires same-day intervals with end >= start. For a night shift crossing midnight, supply complete dates on all four endpoints; a recurring window across multiple dates must be split into separate intervals. These are ideal interval durations, not a payroll policy; unpaid breaks and time-zone changes are omitted. Rounding separate components may leave small reconciliation differences.
Try the example
Use the original values or change them to test your understanding.