How to calculate overtime pay
✓ Verified in LibreOffice 25.8.7.3Split hours into regular and overtime at 1.5x past 40 — payroll's most common formula.
The formula
| App | Formula | Notes |
|---|---|---|
| Excel | =MIN(A2,40)*B2+MAX(0,A2-40)*B2*1.5 | A2=total hours, B2=hourly rate. Change 40 and 1.5 to your rules. |
| Google Sheets | =MIN(A2,40)*B2+MAX(0,A2-40)*B2*1.5 | Identical. |
| LibreOffice Calc | =MIN(A2,40)*B2+MAX(0,A2-40)*B2*1.5 | Identical. |
How it works
MIN caps the regular hours at 40 and MAX floors the overtime at zero, so the pair works for both sides of the threshold: 45 hours at $20 is 40×20 + 5×30 = $950, and a 35-hour week is simply 35×20 with no negative overtime. The same MIN/MAX pattern handles any tiered rate — double time past 60 just adds another MAX term.
Verified, not just documented
We ran =MIN(A2,40)*B2+MAX(0,A2-40)*B2*1.5 in LibreOffice 25.8.7.3 (headless, with forced recalculation) and it returned 950 — exactly the expected result. Every formula here is confirmed by actually executing it.