Excel guide · 6 min read
How to calculate overtime in Excel
=MAX(0,total_hours-40) and regular hours are =MIN(40,total_hours), with 40 typed in its own cell rather than in the formula. For overtime over 8 hours a day, use =MAX(0,day_hours-8) on each day. Hours must be decimal numbers first: a time difference such as 9:30 is stored as 0.396 of a day, so multiply by 24. Overtime pay is overtime hours x rate x the multiplier (for example 1.5) from another input cell.Sample data
Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.
| Row | A | B | C | D | E | F | G | H | I | J | K |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Day | In | Out | Break (min) | Paid hours | Daily regular | Daily OT | Double time | Daily OT after (h) | 8 | |
| 2 | Mon | 7:00 AM | 4:30 PM | 30 | 9 | 8 | 1 | 0 | Weekly OT after (h) | 40 | |
| 3 | Tue | 7:00 AM | 3:30 PM | 30 | 8 | 8 | 0 | 0 | Double time after (h/day) | 12 | |
| 4 | Wed | 6:00 AM | 7:00 PM | 30 | 12.5 | 8 | 4 | 0.5 | OT multiplier | 1.5 | |
| 5 | Thu | 7:00 AM | 3:30 PM | 30 | 8 | 8 | 0 | 0 | Double-time multiplier | 2 | |
| 6 | Fri | 7:00 AM | 5:00 PM | 30 | 9.5 | 8 | 1.5 | 0 | Hourly rate ($) | 22 | |
| 7 | Sat | 10:00 PM | 2:30 AM | 15 | 4.25 | 4.25 | 0 | 0 |
Download this sheet as an Excel practice file (.xlsx)
Step 1: turn clock times into decimal hours (x24 and MOD)
Excel stores a time as a fraction of a day. Monday's shift runs from 7:00 to 16:30, and =C2-B2 shows 9:30, but the value underneath is 0.396. Multiply that by the $22 rate and you get $8.71 for a whole day. Every overtime formula below compares hours with numbers like 8 and 40, so convert to decimal hours first: subtract, multiply by 24, then take off the break in minutes divided by 60.
Saturday is a night shift from 22:00 to 2:30. Out minus In is negative (-0.8125), which shows as ##### and breaks every total. MOD(C7-B7,1) wraps the negative value around the clock to 0.1875 of a day, 4.5 hours, and does nothing to day shifts, so one formula fills the whole column. More on this in time difference over midnight.
ROUND(...,2) keeps each day at two decimals, so totals match what someone checks by hand. This sheet counts the night shift on the day it started; if your policy splits a shift at midnight, split the row too.
Result: 4.25
4.25 paid hours for the 22:00 to 2:30 shift, less a 15-minute break, instead of a negative time.
Weekly overtime: hours over 40
Add the paid hours, take away the weekly threshold in K2, and stop at zero so a short week does not produce negative overtime. The week totals 51.25 hours, so 11.25 are overtime and =MIN(K2,SUM(E2:E7)) gives the 40 regular hours.
The threshold lives in K2, not inside the formula. Overtime rules differ by country, state, industry and contract, and they change. Type the number that applies to you; this page only does the arithmetic and says nothing about what anyone is owed. Ask your employer, payroll provider or local labor authority which rule applies.
If you keep the hours as times instead of decimals, compare with K2/24, not with TIME(40,0,0). TIME wraps any hour over 23 back into a single day, so TIME(40,0,0) is 16:00, and a "40-hour" test on it starts overtime after 16 hours.
Result: 11.25
11.25 weekly overtime hours (51.25 - 40).
Daily overtime: hours over 8 in a day
Some rules count overtime per day instead of, or as well as, per week. Then each row needs its own test: =MAX(0,E2-$K$1) gives Monday's 1 hour over the daily limit in K1. The $ keeps K1 fixed as you fill down.
For the whole week in one cell, SUMPRODUCT tests every day at once. (E2:E7>K1) is 1 for days over the limit and 0 for the rest, so only those days add their excess: 1 (Monday) + 4.5 (Wednesday) + 1.5 (Friday) = 7. The 4.25-hour Saturday adds nothing.
Result: 7
7 daily overtime hours: 1 on Monday, 4.5 on Wednesday and 1.5 on Friday.
Daily and weekly rules together, without double counting
Where both rules apply, adding them is the most common mistake: 7 daily + 11.25 weekly = 18.25 overtime hours in a 51.25-hour week. Wednesday's long day counts once as daily overtime and again inside the weekly total.
Apply the weekly test only to hours that were not already overtime. Column F caps each day at the daily limit (=MIN(E2,$K$1)), so its total, 44.25, is the regular hours before the weekly test. Weekly overtime is then =MAX(0,SUM(F2:F7)-K2), 4.25 hours, and total overtime is daily plus that: 6.5 + 4.25 = 10.75 at time-and-a-half, plus 0.5 hours of double time from the next section. Regular, overtime and double time add back up to 51.25, which is the check to keep on the sheet.
Under a single weekly rule, the same week has 11.25 overtime hours. The combined rule gives the same total here, because the week runs well past 40; in a 38-hour week with one 12-hour day, the weekly rule gives 0 and the daily rule 4. Whichever rule you use, put it in the input cells and leave the formulas alone.
Result: 10.75
10.75 overtime hours at the overtime rate: 6.5 daily (8 to 12 hours) plus 4.25 weekly, with nothing counted twice.
Double time over a second threshold
A second tier works the same way. Column G stops daily overtime at the double-time limit in K3 with =MAX(0,MIN(E2,$K$3)-$K$1), and column H takes whatever is above it with =MAX(0,E2-$K$3). Wednesday's 12.5 hours split into 8 regular, 4 overtime and 0.5 double time. The three columns always add back up to the paid hours, so you can check any row by eye.
Weekly tiers (for example, double time after a second weekly threshold) follow the same MIN/MAX pattern on the weekly total. Keep each limit in its own input cell.
Result: 0.5
0.5 hours of double time, from Wednesday's 12.5-hour day.
Overtime pay
Pay each bucket at its rate: regular hours at the base rate in K6, overtime at rate x K4, double time at rate x K5. LET names the weekly overtime once (wk) so it can be taken out of regular hours and added to overtime in the same formula.
Regular is 44.25 - 4.25 = 40 hours at $22 ($880), overtime 10.75 hours at $33 ($354.75) and double time 0.5 hours at $44 ($22). Under a weekly-only rule the same week pays =ROUND(MIN(K2,SUM(E2:E7))*K6+MAX(0,SUM(E2:E7)-K2)*K6*K4,2), $1,251.25. The $5.50 gap is the half hour of double time. Gross pay for a whole team, and an overtime premium written as one SUMPRODUCT, are in payroll and timesheet formulas; deductions and net pay are in payroll formulas in Excel.
Result: 1256.75
$1,256.75 gross for the week: $880 regular + $354.75 overtime + $22 double time.
Which method to pick
Only a weekly rule: =MAX(0,total-threshold) and =MIN(threshold,total), nothing else needed. Only a daily rule: a MAX(0,day-limit) column, or the SUMPRODUCT for the week. Both rules, or a double-time tier: the three helper columns (capped regular, daily overtime, double time), then the weekly test on the capped column. It looks longer, but every number is visible and adds back to the paid hours, which matters when someone questions a payslip. For hours logged as a list of many people and weeks, total each person's week first with SUMIFS (hours per week from a timesheet), then apply the same formulas.
Common mistakes
Multiplying a time difference straight by the rate
9:30 is stored as 0.396, so the pay comes out at a 24th of the real amount. Multiply by 24 first, or keep a decimal-hours column.
Adding daily and weekly overtime
Hours over the daily limit are inside the weekly total too. Run the weekly test on hours capped at the daily limit, as in column F.
Comparing time values with TIME(40,0,0)
TIME wraps hours over 23, so TIME(40,0,0) is 16:00. Use 40/24 for times, or convert to decimal hours and compare with 40.
Typing 40, 8 or 1.5 inside the formulas
When the rule or contract changes, every formula must be found and edited. Keep thresholds and multipliers in input cells with $ references.
FAQ
What is the formula for overtime over 40 hours in Excel?
=MAX(0,total_hours-40) for overtime hours and =MIN(40,total_hours) for regular hours, with 40 in a cell such as K2 so it can change. The hours must be decimal numbers; multiply time values by 24 first.
How do I calculate overtime over 8 hours a day in Excel?
Add a column with =MAX(0,day_hours-8) and sum it, or use =SUMPRODUCT((E2:E7>8)*(E2:E7-8)) for the week in one cell. Put the 8 in an input cell.
How do I calculate time and a half in Excel?
Multiply overtime hours by the rate and by 1.5 from an input cell: =overtime_hours*rate*multiplier. Add regular hours times the rate for the gross pay, and wrap it in ROUND(...,2) for cents.
Why is my overtime negative or showing #####?
Either a short week gives a negative number (wrap it in MAX(0,...)) or a shift crosses midnight and Out minus In is negative (use MOD(Out-In,1)).
Does Excel know the overtime laws for my state or country?
No. Excel has no built-in overtime rules. Whether overtime is daily, weekly or both, and at what multiplier, depends on where you work and your contract, so check with your employer, payroll provider or labor authority and type those numbers into the input cells.
Functions used
MAXLargest numberMINSmallest numberMODRemainder after divisionROUNDRounds to digitsSUMAdds numbersSUMPRODUCTMultiply then addLETNames values inside a formulaTIMEBuilds a time
Related guides
- Payroll and timesheet formulas in Excel
- Payroll formulas in Excel: a gross-to-net payroll register
- How to calculate hours worked in Excel
- Calculate hours worked overnight in Excel
- How to calculate schedule adherence in Excel
- How to calculate a weighted score in Excel
- How to normalize data in Excel
Updated