Math & Trig
Timesheet overtime formula: split regular, 1.5x overtime, and 2x double-time
Cap regular hours at 40, isolate 1.5x overtime hours in a middle band, and calculate 2.0x double-time and gross pay with MIN and MAX.
Decompose total weekly hours into three non-overlapping pay bands
When an employee logs 52 hours under a 40-hour regular cap and 50-hour double-time threshold, nested IF formulas quickly tangle.
MIN and MAX slice each band cleanly: Regular = MIN(Hours, 40) -> 40; Overtime (1.5x) = MAX(0, MIN(Hours, 50) - 40) -> 10; Double-Time (2.0x) = MAX(0, Hours - 50) -> 2. Notice 40 + 10 + 2 = 52 total hours.
| WorkedHrs (A) | Regular (<=40) | 1.5x OT (40–50) | 2.0x DT (>50) | Gross @ $30/hr |
|---|---|---|---|---|
| 52 | 40 | 10 | 2 | (40 + 15 + 4) * 30 = 1,770 |
| 44 | 40 | 4 | 0 | (40 + 6 + 0) * 30 = 1,380 |
| 38 | 38 | 0 | 0 | (38 + 0 + 0) * 30 = 1,140 |
Overtime band and total gross pay formulas
Weighting overtime by 1.5 and double-time by 2.0 computes total equivalent straight-time hours (59) multiplied by BaseRate.
=(MIN(A2, B2) + MAX(0, MIN(A2, C2) - B2) * 1.5 + MAX(0, A2 - C2) * 2) * D2Isolate 1.5x overtime hours only
MIN(A2, C2) caps hours at the double-time ceiling before subtracting the regular threshold.
=MAX(0, MIN(A2, C2) - B2)Inputs and interpretation
The 40/50-hour thresholds and 1.5x/2x multipliers are a hypothetical policy, not a universal legal overtime rule. Require 0 <= regular cap <= double-time cap, nonnegative hours and rate, and a consistent period. Daily overtime, special days, exemptions, deductions and taxes are outside this example. Confirm actual payroll rules before adapting it.
Try the example
Use the original values or change them to test your understanding.