How to Calculate Overtime Pay in Excel: Free 2026 Formula Template

The Short Answer

To calculate overtime pay in Excel for 2026, use this single formula in your weekly pay cell:

`=MIN(B2,40)*C2 + MAX(0,B2-40)*C2*1.5`

Where B2 is total hours worked in the week and C2 is the hourly rate. This formula automatically caps regular pay at 40 hours and applies the FLSA-mandated 1.5× time-and-a-half premium to every hour above 40.

For California and other daily-overtime states, you need a second layer that checks each *day* (not just the week). We cover both below — plus double time (2×), the 7th-consecutive-day rule, and how to handle the 2025–2028 federal "No Tax on Overtime" deduction inside the same spreadsheet.

If you'd rather skip the spreadsheet entirely, our free overtime pay calculator handles all 50 states' rules without a single formula.

The Core Excel Overtime Formula (FLSA Federal Rule)

The Fair Labor Standards Act requires non-exempt employees to be paid 1.5× their regular rate for every hour worked beyond 40 in a single workweek. Translated into Excel:

| Cell | Label | Value |

|------|-------|-------|

| A2 | Employee | Jane Doe |

| B2 | Total Hours | 47 |

| C2 | Hourly Rate | $22 |

| D2 | Regular Pay | `=MIN(B2,40)*C2` |

| E2 | Overtime Pay | `=MAX(0,B2-40)*C2*1.5` |

| F2 | Gross Weekly Pay | `=D2+E2` |

For Jane (47 hours at $22/hour): regular pay = $880, overtime = 7 × $33 = $231, gross = $1,111.

Why MIN and MAX matter

`MIN(B2,40)` guarantees the regular-pay leg never exceeds 40 hours, even when somebody works 60. `MAX(0,B2-40)` guarantees the overtime leg never goes negative when somebody works fewer than 40. Together they make the formula safe to copy down 500 rows without breaking on part-time employees.

Time and a Half Formula in Excel

"Time and a half" simply means 1.5×. If you only want the *premium* (the extra 0.5× that the IRS treats as Qualified Overtime Compensation for the 2025–2028 deduction), use:

`=MAX(0,B2-40)*C2*0.5`

This isolates the "half" portion of time-and-a-half — the exact number that should appear in W-2 Box 14 (QOT) for employees claiming the No Tax on Overtime deduction. See our guide on how overtime is taxed in 2026 for the IRS rules.

Double Time (2×) Formula

States like California require double time after 12 hours in a day or after 8 hours on the seventh consecutive workday. To layer 2× pay on top of 1.5×:

`=MIN(B2,40)*C2 + MIN(MAX(0,B2-40),8)*C2*1.5 + MAX(0,B2-48)*C2*2`

This says: first 40 hours at straight time, next 8 hours at 1.5×, anything above 48 at 2×. Tune the 8 and 48 thresholds to match your state's rule.

Daily Overtime Formula (California, Alaska, Nevada, Colorado)

For California, overtime kicks in after 8 hours a day, not just 40 a week. Lay out your spreadsheet with one column per day:

| | Mon | Tue | Wed | Thu | Fri | Sat | Sun |

|---|---|---|---|---|---|---|---|

| Hours | 10 | 9 | 8 | 8 | 5 | 0 | 0 |

For each day's regular pay cell: `=MIN(B2,8)*$C$2`

For each day's 1.5× pay cell: `=MIN(MAX(0,B2-8),4)*$C$2*1.5`

For each day's 2× pay cell: `=MAX(0,B2-12)*$C$2*2`

Sum the three rows for total weekly gross. For more on California's rules, see 7th-day overtime in California and our California overtime calculator.

Biweekly Payroll Formula

Many employers run biweekly payroll but the FLSA still requires you to calculate overtime on a single workweek basis — never average two weeks. Two separate weekly columns, then sum the gross:

`Week1Gross = MIN(W1,40)*Rate + MAX(0,W1-40)*Rate*1.5`

`Week2Gross = MIN(W2,40)*Rate + MAX(0,W2-40)*Rate*1.5`

`BiweeklyGross = Week1Gross + Week2Gross`

Combining the two weeks into a single 80-hour bucket is one of the most common Excel mistakes, and it almost always underpays the employee — exactly the violation biweekly overtime calculations 2026 warns about.

Handling Tipped Employees, Bonuses, and Shift Differentials

Excel overtime gets harder when the "regular rate" itself isn't a fixed number. Under FLSA §207(e), the regular rate must include nondiscretionary bonuses, shift differentials, and commissions. Use this weighted-average formula:

`RegularRate = (StraightWages + Bonuses + Differentials) / TotalHoursWorked`

`OvertimePremium = MAX(0,TotalHours-40) * RegularRate * 0.5`

See weighted average overtime calculation 2026 for the full method and IRS-aligned examples.

Free Excel Overtime Template (Build It in 60 Seconds)

• . Open a blank workbook.
• . In row 1, label columns: Employee, Week Start, Hours Worked, Hourly Rate, Regular Pay, Overtime Pay, Gross.
• . In E2 paste: `=MIN(C2,40)*D2`
• . In F2 paste: `=MAX(0,C2-40)*D2*1.5`
• . In G2 paste: `=E2+F2`
• . Drag E2:G2 down for every employee.

Save it as `Overtime_2026.xlsx`. You now have a fully functional weekly overtime calculator.

Common Excel Overtime Mistakes

• Using ROUNDUP on hours. Round dollar amounts, not hours. Rounding 39.6 up to 40 cheats the employee out of 24 minutes' pay.
• Putting the multiplier inside a constant cell with formatting. `1.5` formatted as `150%` will quietly multiply by 1.5 — but if anyone changes the format, the math breaks.
• Averaging biweekly hours. As above, FLSA requires single-workweek calculations.
• Forgetting paid-but-not-worked hours. Holiday pay and PTO do not count toward the 40-hour threshold under federal law. See how holiday pay and overtime interact.

FAQ

What is the Excel formula for time and a half?

`=MIN(Hours,40)*Rate + MAX(0,Hours-40)*Rate*1.5` calculates regular plus 1.5× overtime in one cell.

How do I calculate overtime in Excel for California?

Use a daily layout with three formulas per day: `MIN(Day,8)*Rate` for regular, `MIN(MAX(0,Day-8),4)*Rate*1.5` for time-and-a-half, and `MAX(0,Day-12)*Rate*2` for double time. Sum across the week.

Can Excel handle the 2025 No Tax on Overtime deduction?

Yes. Compute the 0.5× premium separately with `=MAX(0,Hours-40)*Rate*0.5`. That number is the Qualified Overtime Compensation the IRS allows as a deduction on Schedule 1-A for tax years 2025–2028.

Should I use IF or MIN/MAX for overtime formulas?

`MIN` and `MAX` are shorter, faster, and easier to audit. A nested `IF` like `=IF(B2>40, 40*C2 + (B2-40)*C2*1.5, B2*C2)` works too but is harder to extend when you add double time or daily rules.

Does Google Sheets use the same formulas?

Yes — every formula in this guide works identically in Google Sheets, LibreOffice Calc, and Apple Numbers.

Calculate Your Overtime Without the Spreadsheet

If you don't want to maintain Excel formulas state by state, our free overtime pay calculator has the 2026 FLSA rule, all 50 state rules, daily overtime for California/Alaska/Nevada/Colorado, double time, and the 2025–2028 No Tax on Overtime deduction baked in. No download, no signup.

Related Reading

• How to Calculate Time and a Half Pay
• Biweekly Overtime Pay Calculation 2026
• Weighted Average Overtime Calculation 2026
• How Overtime Is Taxed 2026

---

*Sources: U.S. Department of Labor — FLSA Overtime; Fact Sheet #23: Overtime Pay; IRS Notice 2025-69 (Qualified Overtime Compensation). Last reviewed June 30, 2026.*

Run your own numbers

If you would rather skip the formulas entirely, the time and a half calculator does the same math in one screen, and the full index of overtime calculators lists every other tool we maintain.