Anything over 40 hours a week is usually paid at 1.5×. A clean formula splits the hours and totals regular plus overtime pay in one step.
MIN and MAX split the hours so you never double-count.
The example
An employee worked 46 hours at $20/hour. The first 40 are regular; 6 are overtime.
| A | B | |
|---|---|---|
| 1 | Item | Value |
| 2 | Hours worked | 46 |
| 3 | Hourly rate | $20 |
| 4 | Gross pay | $980 |
The formula
MIN caps regular hours at 40; MAX pulls out anything above 40 as overtime:
How it works
The two halves:
MIN(B2,40)gives regular hours — 40 even though 46 were worked.MAX(B2-40,0)gives overtime hours — 6, and never a negative number for short weeks.- Regular pay is 40 × $20 = $800; overtime is 6 × $20 × 1.5 = $180.
- Add them for gross pay of $980. If hours are 40 or fewer, the overtime term is zero.
The MAX(...,0) guard is what keeps a 35-hour week from producing negative overtime.
Try it: interactive demo
Enter hours and rate; see regular, overtime, and gross pay.
Variations
Double time over 60 hours
Add a third tier for hours beyond 60 at 2×.
Overtime hours only
Just the OT count, for a separate column.
Pitfalls & errors
Without the MAX(...,0) guard, weeks under 40 hours produce a negative overtime term and undercount pay.
Overtime rules vary — some states use daily overtime over 8 hours. Confirm the rule that applies before relying on the 40-hour split.
Practice workbook
Frequently asked questions
What counts as overtime?
Why use MIN and MAX instead of IF?
How do I add double time?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class