Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to calculate overtime pay in Excel and Google Sheets
Topic:Finance basicsExcel & Google Sheets
=MIN(A2,40)*B2+MAX(0,A2-40)*B2*1.5✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| Hours Worked | Hourly Rate | Total Pay |
| 45 | 20 | |
| 38 | 25 | |
| 52 | 18 | |
| 40 | 30 |
=MIN(A2,40)*B2+MAX(0,A2-40)*B2*1.5→950
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Column A = Total Hours Worked, Column B = Hourly Rate.
- 2In C2 enter =MIN(A2,40)*B2+MAX(0,A2-40)*B2*1.5.
- 3MIN(A2,40) caps regular hours at 40; MAX(0,A2-40) returns overtime hours (0 if none); multiply overtime by 1.5x rate.
- 4Adjust 40 to your jurisdiction's threshold and 1.5 to your overtime multiplier.
Tips
- Store the overtime threshold (40) and multiplier (1.5) in named cells (e.g., OT_Threshold, OT_Rate) so they're easy to update for different contracts or regions.
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
How do I handle double-time for hours over 60?
Nest additional tiers: =MIN(A2,40)*B2+MIN(MAX(0,A2-40),20)*B2*1.5+MAX(0,A2-60)*B2*2.
What if the overtime threshold is daily (e.g., 8 hours/day)?
Apply the same formula per row for each day, with A2 = hours that day. Then SUM the daily totals.
More on Finance basics
See all →How-to
How to calculate compound interest
Calculate a compounded closing balance from principal, annual nominal rate, compounding frequency and years, then separate the interest earned.
How-toHow to calculate the effective annual interest rate
Convert a positive nominal annual rate and a whole-number compounding frequency into an effective annual rate with EFFECT.
How-toHow to calculate the break-even point in units
Divide fixed costs by unit contribution margin and round up to whole units, with an explicit message when the margin is nonpositive.
How-toHow to calculate tiered commission
Apply different commission rates by sales tier using IFS. Works in Excel 2019/365 and Google Sheets.
How-toHow to calculate net pay
Calculate take-home (net) pay by subtracting every deduction from gross salary, with worked examples for fixed amounts, percentage tax, and slab-based tax. Excel and Google Sheets.
How-toHow to calculate gross margin
Compute gross margin percentage as (price − cost) / price. Identical in Excel and Google Sheets.