Excel has no overtime function, but you only need MIN, MAX and one unit conversion to build an overtime sheet you can trust. This guide builds a weekly sheet from clock times, splits the total into regular and overtime hours, prices them, and then adds daily overtime, California-style double time, biweekly pay periods and nondiscretionary bonuses. If you only need a quick answer for one week, the overtime calculator does the same math without a spreadsheet.
Set up the weekly timesheet
Put one day per row, with the workweek in rows 2–8 and totals in row 9:
- A Date
- B Time In
- C Time Out
- D Unpaid break in minutes
- E Hours as a time value
- F Hours in decimal
Enter times as 7:00 AM or 7:00; Excel recognizes either. In E2 and F2, then fill both down to row 8:
=MOD(C2-B2,1)-D2/1440
=ROUND(E2*24,2)Format column E as h:mm and column F as 0.00 (Format Cells, Ctrl+1 or Cmd+1 → Custom). MOD(…,1) keeps a shift that crosses midnight positive, and dividing the break by 1,440 turns minutes into Excel’s fraction of a day.
| A Date | B In | C Out | D Break | E Hours | F Decimal |
|---|---|---|---|---|---|
| Mon 10/5 | 7:00 AM | 4:30 PM | 30 | 9:00 | 9.00 |
| Tue 10/6 | 7:00 AM | 5:15 PM | 30 | 9:45 | 9.75 |
| Wed 10/7 | 7:00 AM | 3:30 PM | 30 | 8:00 | 8.00 |
| Thu 10/8 | 7:00 AM | 6:00 PM | 30 | 10:30 | 10.50 |
| Fri 10/9 | 7:00 AM | 4:00 PM | 30 | 8:30 | 8.50 |
| Sat 10/10 | 8:00 AM | 11:30 AM | 0 | 3:30 | 3.50 |
| Sun 10/11 | 0:00 | 0.00 | |||
| Total (row 9) | 49:15 | 49.25 |
The row 9 totals are:
=SUM(E2:E8)
=SUM(F2:F8)Format E9 as [h]:mm. The square brackets let hours run past 24; with plain h:mm the same total would display as 1:15. For more daily formulas, including separate lunch-out and lunch-in punches, see calculate hours worked in Excel.
Split regular and overtime hours
Put the hourly rate in B11 ($22.00 in this example), add labels in A12:A14, and enter these formulas in B12, B13 and B14:
=MIN(F9,40)
=MAX(0,F9-40)
=B12*B11+B13*B11*1.5| Cell | Meaning | Result |
|---|---|---|
| B12 | Regular hours | 40.00 |
| B13 | Overtime hours | 9.25 |
| B14 | Gross pay | $1,185.25 |
Regular pay is 40 × $22 = $880.00 and overtime pay is 9.25 × $22 × 1.5 = $305.25. If you prefer a single cell:
=MIN(F9,40)*$B$11+MAX(0,F9-40)*$B$11*1.5The 40-hour threshold and the 1.5 multiplier come from the Fair Labor Standards Act. FLSA overtime rules explained covers who qualifies and which hours count toward the 40.
Time values vs. decimal hours
Most broken overtime sheets mix the two ways Excel can hold hours. A cell that displays 49:15 actually contains 2.0521, because Excel stores time as a fraction of a 24-hour day. That causes three classic mistakes:
=MAX(0,E9-40)returns 0 every week, because 2.05 is never larger than 40.=E9*B11returns $45.15 instead of $1,083.50, because it multiplies days, not hours, by the rate.=MAX(0,E9-TIME(40,0,0))is also wrong. TIME wraps any hour above 23, so TIME(40,0,0) equals 16:00.
If you must work from the time column, convert inside the formula:
=MAX(0,E9*24-40)
=MAX(0,E9-40/24)The first returns 9.25 decimal hours. The second returns 9:15 as a time value, which needs the [h]:mm format. Pick one system for all pay math; decimal hours are safer because pay rates are quoted per hour, not per day. The guide to convert time to decimal in Excel covers more conversions.
Daily overtime and California-style double time
Some union contracts and a few states pay overtime by the day. For overtime after 8 hours a day, add two columns:
=MIN(F2,8)
=MAX(0,F2-8)California also requires double time after 12 hours in a day. Use three columns instead: G for regular hours, H for overtime at 1.5× and I for double time at 2×:
=MIN(F2,8)
=MAX(0,MIN(F2,12)-8)
=MAX(0,F2-12)Here is the same week with a 13-hour Thursday (7:00 AM to 8:30 PM with a 30-minute break):
| Day | F Decimal | G Regular | H OT 1.5× | I Double 2× |
|---|---|---|---|---|
| Mon | 9.00 | 8.00 | 1.00 | 0.00 |
| Tue | 9.75 | 8.00 | 1.75 | 0.00 |
| Wed | 8.00 | 8.00 | 0.00 | 0.00 |
| Thu | 13.00 | 8.00 | 4.00 | 1.00 |
| Fri | 8.50 | 8.00 | 0.50 | 0.00 |
| Sat | 3.50 | 3.50 | 0.00 | 0.00 |
| Sun | 0.00 | 0.00 | 0.00 | 0.00 |
| Row 9 totals | 51.75 | 43.50 | 7.25 | 1.00 |
The weekly 40-hour test still applies, but only to hours not already paid as daily overtime. Here 43.50 regular hours exceed 40, so 3.50 of them move to the overtime column. Put the final figures in G10, H10 and I10:
=MIN(G9,40)
=H9+MAX(0,G9-40)
=I9That gives 40.00 regular, 10.75 overtime and 1.00 double-time hours, still 51.75 in total. With the $22 rate in B11:
=G10*$B$11+H10*$B$11*1.5+I10*$B$11*2The result is $880.00 + $354.75 + $44.00 = $1,278.75. California also has a seventh-consecutive-day rule that this layout does not model, so add a separate column if anyone can work all seven days of a workweek.
Overtime across a biweekly pay period
A two-week pay period is two separate workweeks for overtime. Never test the 80-hour period total. Keep a daily log with dates in A2:A15 and decimal hours in F2:F15, and add a week number in G2, filled down:
=WEEKNUM(A2,2)Return type 2 starts weeks on Monday. Use 1 for Sunday, or 11–17 for Monday through Sunday if your workweek starts on another day (13 is Wednesday). Then list the two week numbers in J2:J3, put the hourly rate in P1, and enter these formulas in K2, L2, M2 and N2, filled down to row 3:
=SUMIFS($F$2:$F$15,$G$2:$G$15,J2)
=MIN(K2,40)
=MAX(0,K2-40)
=L2*$P$1+M2*$P$1*1.5| J Week | K Hours | L Regular | M Overtime | N Pay at $22 |
|---|---|---|---|---|
| 41 (Oct 5–11) | 30.00 | 30.00 | 0.00 | $660.00 |
| 42 (Oct 12–18) | 50.00 | 40.00 | 10.00 | $1,210.00 |
| Total | 80.00 | 70.00 | 10.00 | $1,870.00 |
Testing the combined 80 hours would show no overtime and pay only $1,760.00, which underpays by $110.00. WEEKNUM restarts at 1 on January 1, so a workweek that spans New Year’s Day gets two numbers. For a key that works across years, use the date each week starts (Monday-start version shown):
=A2-WEEKDAY(A2,2)+1Bonuses and the regular rate
A nondiscretionary bonus, meaning one promised in advance for attendance, production or safety, must be folded into the regular rate before overtime is figured (29 CFR Part 778). The overtime premium is then half the regular rate for each overtime hour, because straight-time pay for every hour is already in the total.
Suppose a different week: 45 hours in F9, $18.00 in B11 and a $135 attendance bonus in B15. In B16, B17 and B18:
=(F9*B11+B15)/F9
=0.5*B16*MAX(0,F9-40)
=F9*B11+B15+B17Worked example: straight-time pay is 45 × $18 = $810.00. Adding the $135 bonus gives $945.00, so the regular rate is $945 ÷ 45 = $21.00 (B16). The premium is 0.5 × $21.00 × 5 = $52.50 (B17), and gross pay is $810.00 + $135.00 + $52.50 = $997.50 (B18).
Paying overtime on the $18 base rate alone would give $990.00, which is $7.50 short.
If a bonus covers several weeks, allocate it across those workweeks and recalculate each week’s regular rate.
Common errors and fixes
- ##### in a cell. The column is too narrow, or a time formula went negative. Widen the column; for shifts past midnight use MOD(C2-B2,1), as explained in Excel night shift hours.
- Weekly total looks far too small. A total of 1:15 instead of 49:15 means the cell uses h:mm. Change it to
[h]:mm. In Google Sheets, choose Format → Number → Duration, or enter [h]:mm under Format → Number → Custom number format. - Times are left-aligned and SUM returns 0. The values are text, often pasted from a time-clock export. Convert them with
=TIMEVALUE(B2)or=--B2, and check for stray spaces. - Overtime shows 9.2500000001. Floating-point arithmetic leaves tiny remainders. Wrap totals in ROUND(…,2) before comparing them with 40 or multiplying by a rate.
- 7:45 typed as 7.45. Clock times and decimal hours are not interchangeable: 7:45 is 7.75 hours, and 0.1 hour is 6 minutes.
- The rate reference drifts when you copy a formula. Lock rate and threshold cells with absolute references such as $B$11.
These formulas follow the federal FLSA weekly rule and a California-style daily rule. This is not legal advice; state law, union contracts and company policy may be stricter, so confirm the rules that apply to your employees.
Frequently asked questions
Does Excel have a built-in overtime function?
No. Overtime is built from MIN and MAX: MIN caps regular hours at the threshold, and MAX returns whatever is left above it, or zero. Multiply each part by its rate and add them to get gross pay.
Why does my overtime formula always return zero?
Your weekly total is probably a time value, not decimal hours. Excel stores 49:15 as about 2.05 days, so subtracting 40 never produces a positive number. Multiply the total by 24 first, or compare it with 40/24 instead of 40.
Can I combine two weeks so the hours average out to 40?
Not for non-exempt employees covered by the FLSA. Each workweek stands alone, so 30 hours one week and 50 the next means 10 overtime hours even though the two-week total is exactly 80. Split the pay period by workweek before applying the 40-hour test.
How do I add holiday double time in Excel?
Federal law does not require holiday premiums, but if your policy pays them, add a column that flags holiday dates, for example with COUNTIF against a list of holiday dates, and route those hours to a double-time column. Decide in writing whether premium holiday hours also count toward the weekly 40 and apply that rule every week.