Overtime Pay Calculator
Calculate overtime earnings with the standard 1.5x rate, splitting regular and overtime hours in a Google Sheets formula.
=IF(hours>40, 40*rate + (hours-40)*rate*1.5, hours*rate) How It Works
Standard overtime rules pay 1.5x (time-and-a-half) the regular hourly rate for hours worked beyond 40 in a week. This formula splits a week’s work into regular and overtime portions and calculates total gross pay.
Syntax
=IF(total_hours > 40, 40 * hourly_rate + (total_hours - 40) * hourly_rate * 1.5, total_hours * hourly_rate)The IF checks whether overtime applies. If total hours exceed 40, it calculates 40 regular hours plus the remainder at 1.5x.
Example
Hourly Rate: $25, Hours Worked: 48
| Component | Hours | Rate | Pay |
|---|---|---|---|
| Regular | 40 | $25.00 | $1,000.00 |
| Overtime | 8 | $37.50 | $300.00 |
| Total | 48 | $1,300.00 |
Formula: =IF(48>40, 40*25 + (48-40)*25*1.5, 48*25)
Result: $1,300.00
Without overtime, 48 hours at $25 would be $1,200 - so the overtime premium adds $100.
Setting Up an Overtime Tracker
| A | B | C | D | E | F |
|---|---|---|---|---|---|
| Week | Hours | Rate | Regular Pay | OT Pay | Total |
| Week 1 | 48 | $25 | =MIN(B2,40)*C2 | =MAX(B2-40,0)C21.5 | =D2+E2 |
| Week 2 | 35 | $25 | =MIN(B3,40)*C3 | =MAX(B3-40,0)C31.5 | =D3+E3 |
| Week 3 | 52 | $25 | =MIN(B4,40)*C4 | =MAX(B4-40,0)C41.5 | =D4+E4 |
| Totals | =SUM(B2:B4) | =SUM(D2:D4) | =SUM(E2:E4) | =SUM(F2:F4) |
Using MIN and MAX avoids the IF statement entirely:
MIN(hours, 40)caps regular hours at 40MAX(hours - 40, 0)gives overtime hours or zero
Variations
Double-Time Rate
Some roles pay 2x for hours beyond a higher threshold (e.g., beyond 60 hours):
=MIN(B2,40)*C2 + MIN(MAX(B2-40,0),20)*C2*1.5 + MAX(B2-60,0)*C2*2This breaks pay into three tiers: regular (0-40), time-and-a-half (40-60), and double-time (60+).
Daily Overtime
In some states, overtime applies after 8 hours per day regardless of weekly total:
=MIN(daily_hours, 8) * rate + MAX(daily_hours - 8, 0) * rate * 1.5Overtime as Percentage of Total Pay
Track what share of your paycheck comes from overtime:
=OT_pay / total_payAnnualized Overtime Impact
Estimate yearly earnings if overtime hours stay consistent:
=weekly_total * 52Pro Tips
-
Overtime multiplier varies - 1.5x is standard in the US, but some contracts, unions, or holidays use 2x or higher
-
Track daily and weekly - some jurisdictions use daily overtime thresholds (e.g., California uses 8 hours/day)
-
Remember taxes - overtime is taxed as regular income, but higher gross pay may push into a higher withholding bracket
-
Salaried equivalent - to check if overtime-eligible work is worth it vs. a salary offer, multiply total weekly pay by 52
-
Use data validation - set a max hours value (e.g., 80) to catch typos that inflate calculations
Common Errors
- Using 1.5 instead of rate * 1.5: The overtime rate is 1.5 times YOUR rate, not a flat $1.50
- Applying OT to all hours: Only hours beyond 40 get the premium - the first 40 are at regular rate
- Forgetting state rules: Some states have daily overtime or different thresholds - check local labor laws
- Mixing gross and net: This formula calculates gross pay - taxes and deductions come out separately