Overtime Calculator
Work out overtime pay for a full week, including time and a half, double time, daily thresholds and blended rates. Then copy the exact Excel and Google Sheets formulas to do the same thing in your own timesheet.
How do you calculate overtime pay?
Overtime pay is your regular rate multiplied by 1.5 for every hour worked over 40 in a workweek. Multiply the regular rate by 1.5, then multiply that by your overtime hours, and add it to your straight-time pay. In Excel the one cell version is =(MIN(A2,40)*B2)+(MAX(A2-40,0)*B2*1.5), where A2 is hours worked and B2 is the hourly rate.
Overtime pay calculator
Hours worked each day
Your overtime estimate
Federal rule applied: hours over 40 in the workweek are paid at 1.5 times the regular rate.
Get the free Excel formulas cheat sheet
A one page reference with the time, IF and MIN or MAX formulas used on this page, so you can build the overtime logic straight into your own timesheet. Enter your email and we will send it over.
No spam. Unsubscribe any time.
The overtime formula, step by step
Every overtime calculation is the same four steps, whatever your state rule is. The part people get wrong is step one, because the regular rate is not always the hourly rate on the offer letter.
Step 1: find the regular rate of pay
Under the Fair Labor Standards Act, the regular rate is total straight-time earnings divided by total hours worked in the week. Nondiscretionary bonuses, shift differentials and production commissions all get added in before you divide. A discretionary holiday gift does not.
Regular rate = (base pay + nondiscretionary bonus) / total hours workedStep 2: split the week into straight, overtime and double-time hours
Apply the daily rule first, then the weekly rule to whatever is left. Hours already paid at a premium under a daily rule are not counted again under the 40 hour rule, which is the single most common source of overpayment in a manual spreadsheet.
Step 3: multiply each bucket by its rate
Straight pay = straight hours x regular rate
Overtime pay = overtime hours x regular rate x 1.5
Double-time pay = double-time hours x regular rate x 2Step 4: add the buckets together
The sum is gross pay for the week, before taxes and deductions. The overtime premium, which is what payroll usually reports separately, is the extra half rate on overtime hours plus the extra full rate on double-time hours.
Worked example: 48 hours in a federal week
Take an employee paid $25 an hour who works 8, 8, 8, 10, 10, 4 and 0 hours across the week, and earns a $100 production bonus. Total hours are 48.
| Step | Working | Result |
|---|---|---|
| Base straight-time earnings | 48 x $25 | $1,200.00 |
| Regular rate | ($1,200 + $100) / 48 | $27.0833 |
| Straight-time hours | First 40 hours | 40.00 |
| Overtime hours | 48 minus 40 | 8.00 |
| Straight-time pay | 40 x $27.0833 | $1,083.33 |
| Overtime pay | 8 x $27.0833 x 1.5 | $325.00 |
| Gross pay | Straight plus overtime | $1,408.33 |
Notice what the bonus did. Ignoring it and paying overtime at $25 x 1.5 would have produced $1,300 plus the $100 bonus, or $1,400. The correct figure is $1,408.33. The $8.33 gap is the overtime premium owed on the bonus, and it is a routine wage and hour finding.
Overtime rules reference: federal and daily-threshold states
Federal law sets a floor, not a ceiling. Where a state rule is more generous, the state rule wins. Four states apply a daily threshold on top of the federal weekly rule.
| Rule | Overtime at 1.5x | Double time at 2x | Notes |
|---|---|---|---|
| Federal FLSA | Over 40 hours in a workweek | Not required | No federal daily overtime requirement |
| California | Over 8 hours in a day, over 40 in a week, and the first 8 hours on the 7th consecutive day | Over 12 hours in a day, and beyond 8 hours on the 7th consecutive day | The most complex rule set in the country |
| Alaska | Over 8 hours in a day or over 40 in a week | Not required | Applies to covered employers |
| Colorado | Over 12 hours in a day, over 12 consecutive hours, or over 40 in a week | Not required | Whichever produces the most pay |
| Nevada | Over 8 hours in a 24 hour period, or over 40 in a week | Not required | The daily rule applies only below a wage threshold tied to the state minimum wage |
This is a general reference, not legal advice. Confirm the current thresholds for your state and industry with the US Department of Labor Fact Sheet #23 on FLSA overtime pay and your state labor agency before running payroll.
What is the formula for overtime in Excel?
There are three formulas worth knowing, and which one you need depends on whether your timesheet stores hours as plain numbers or as real time values.
Weekly overtime, hours stored as numbers
With total hours in A2 and the hourly rate in B2, one cell handles the whole week.
=(MIN(A2,40)*B2)+(MAX(A2-40,0)*B2*1.5)MIN(A2,40) caps straight time at 40, and MAX(A2-40,0) returns zero instead of a negative number in a short week. Splitting the two out into separate columns makes it much easier to audit.
Daily overtime over 8 hours
With hours for one day in C2, these two formulas split that day into straight and overtime hours.
Straight hours = MIN(C2,8)
Overtime hours = MAX(C2-8,0)Overtime when the timesheet holds clock in and clock out times
If D2 is the clock-in and E2 is the clock-out, Excel stores those as fractions of a day, so you have to multiply by 24 to get decimal hours before any rate maths.
Hours worked = (E2-D2)*24
Overtime pay = MAX(((E2-D2)*24)-8,0)*B2*1.5For more on entering and formatting time values, see our guide to inserting the current time in Excel, and the walkthrough of multiplying in Excel for the rate side of the calculation.
How do you calculate overtime in Google Sheets?
Google Sheets uses the same function names, so the formulas above transfer directly. The differences are small but they do bite.
| Task | Excel | Google Sheets |
|---|---|---|
| Weekly overtime pay | =(MIN(A2,40)*B2)+(MAX(A2-40,0)*B2*1.5) | Identical |
| Argument separator | Comma, or semicolon in some regional settings | Always a comma |
| Hours over 24 displayed correctly | Custom format [h]:mm | Format, Number, Duration |
| Apply the formula down a column | Fill down, or a table formula | =ARRAYFORMULA(MAX(A2:A-40,0)*B2:B*1.5) |
| Conditional overtime logic | IF | IF, see our IF function guide for Google Sheets |
How do you calculate overtime for two different pay rates?
An employee who works 30 hours at $20 as a server and 15 hours at $18 in the kitchen does not get overtime at either rate. The FLSA default is a weighted average, sometimes called the blended rate.
Blended rate = ((30 x $20) + (15 x $18)) / 45
= ($600 + $270) / 45
= $19.3333The 5 overtime hours are then paid at $19.3333 x 1.5, which is $29.00 an hour. Enter the blended figure in the hourly rate box above and the calculator will handle the rest. Our payroll template keeps each rate on its own line and computes the weighted average for you.
Troubleshooting overtime formulas in a spreadsheet
My total hours show as a time like 8:00 instead of 8
The cell is formatted as time, not as a number. Multiply the time value by 24 and format the result as a number with two decimals. A common fix is =(E2-D2)*24 in a helper column that everything else references.
My weekly total resets after 24 hours
Standard time formats roll over at midnight. Apply the custom number format [h]:mm in Excel, or Format, Number, Duration in Google Sheets, so a 48 hour total displays as 48:00 rather than 0:00.
I get a #VALUE! error on the subtraction
One of the cells holds text that looks like a time. Retype the entry, or wrap it with TIMEVALUE(). If the shift crosses midnight, use =MOD(E2-D2,1)*24 so the result does not go negative.
My overtime hours are being counted twice
This happens when a daily overtime column and a weekly overtime column both run against the same hours. Calculate daily premium hours first, then apply the weekly rule only to the remainder: =MAX(TotalHours-40-DailyPremiumHours,0).
The result is a few cents off the payroll system
Almost always rounding on the regular rate. Keep the full precision regular rate in the formula and round only the final pay figure with =ROUND(total,2). Rounding the rate to two decimals first compounds the error across every overtime hour.
Run overtime on a real timesheet, not a one week estimate
The Simple Sheets Employee Timesheet and Payroll templates carry the daily and weekly logic, the blended rate and the premium split across a full pay period, in both Excel and Google Sheets.
See the Employee Timesheet templateOvertime calculator FAQ
How do you calculate overtime pay?
Multiply the regular rate of pay by 1.5, then multiply that by the number of overtime hours, and add it to straight-time pay. For an employee at $25 an hour working 48 hours in a federal week, that is 40 x $25 plus 8 x $37.50, or $1,300 gross.
What is the formula for overtime in Excel?
With total hours in A2 and the hourly rate in B2, use =(MIN(A2,40)*B2)+(MAX(A2-40,0)*B2*1.5). MIN caps straight time at 40 hours and MAX returns zero rather than a negative value when the week is under 40 hours.
How do you calculate overtime in Google Sheets?
The same formula works because Google Sheets uses the identical MIN and MAX functions. To apply it down a whole column at once, wrap it in ARRAYFORMULA, and format duration cells with Format, Number, Duration so totals over 24 hours display correctly.
Is overtime always time and a half?
Not always. Federal law requires at least 1.5 times the regular rate over 40 hours a week, and does not require double time at all. California requires double time over 12 hours in a day and beyond 8 hours on the seventh consecutive workday. Union contracts often set higher multipliers.
Does paid time off count toward the 40 hour overtime threshold?
Under federal law, no. Only hours actually worked count toward the 40 hour threshold, so vacation, sick leave and holiday hours are excluded. An employee with 8 holiday hours and 36 worked hours has 44 paid hours but no federal overtime. Some state rules and contracts are more generous.
How do you calculate overtime for someone paid two different rates?
Use a weighted average. Add all straight-time earnings across both rates, divide by total hours worked, and pay overtime at 1.5 times that blended rate. Thirty hours at $20 plus fifteen at $18 gives a blended rate of $19.33 and an overtime rate of $29.00.
Do bonuses change the overtime rate?
Nondiscretionary bonuses do. Production, attendance and commission bonuses must be added to straight-time earnings before dividing by hours worked, which raises the regular rate and therefore the overtime rate. Truly discretionary gifts, like a surprise holiday bonus, are excluded.
How do you calculate overtime over 8 hours per day in Excel?
Split each day into two columns. Straight hours are =MIN(C2,8) and daily overtime hours are =MAX(C2-8,0). Sum each column across the week, then apply the weekly 40 hour rule only to hours that were not already paid as daily overtime.
More free tools and templates
Project accrued paid time off and year-end balance.
Track hours, breaks and overtime across a pay period.
Multiple rates, blended overtime and gross to net.
Build the schedule before the overtime happens.
Every Simple Sheets HR spreadsheet in one place.
Calculators and cheat sheets, no cost.
Last updated: 23 August 2026. General information only, not legal or payroll advice.
