Excel can calculate overtime pay by separating regular hours from hours above a threshold set by the applicable pay policy, then applying the specified overtime rate.
A few careful choices about time entry, pay periods, and rounding help prevent common timesheet errors.
Set up the timesheet
For a simple weekly timesheet, use these columns:
- A: Employee
- B: Hours worked during the week
- C: Hourly rate
- D: Regular hours
- E: Overtime hours
- F: Total pay
Enter hours as decimal numbers, such as 40 or 7.5.
In a separate cell, such as H1, enter the regular-hours threshold for the week; for this example, use 40.
In H2, enter the overtime multiplier, such as 1.5.
Calculate regular and overtime hours
In D2, calculate regular hours, capped at the threshold:
=MIN(B2,$H$1)In E2, calculate only the hours above that threshold:
=MAX(0,B2-$H$1)Copy both formulas down the columns.
The dollar signs keep the threshold cell fixed when you fill the formulas into additional rows.
If your policy uses a different threshold, update H1.
Calculate total pay with an overtime multiplier
In F2, multiply regular hours by the base rate and overtime hours by the base rate times the multiplier:
=D2*C2+E2*C2*$H$2For example, an employee who works 46 hours at $20 per hour, with a 40-hour threshold and a 1.5 multiplier, has 40 regular hours and 6 overtime hours.
The formula returns $1,010: 40 × $20 + 6 × $20 × 1.5.
Format column F as Currency or Accounting.
The formula returns a numeric value, so currency formatting changes how the result appears without changing the calculation.
Use a single formula when you do not need separate hour columns
If total hours are in B2, the hourly rate in C2, the threshold in H1, and the multiplier in H2, you can calculate pay directly:
=MIN(B2,$H$1)*C2+MAX(0,B2-$H$1)*C2*$H$2This is convenient for a compact payroll summary.
Separate regular and overtime columns are easier to review when you need to check hours or explain a pay result.
Calculate hours from clock-in and clock-out times
If a shift starts in A2 and ends in B2, enter times as Excel time values, such as 9:00 AM and 5:30 PM.
To calculate elapsed hours, including a shift that crosses midnight, use:
=MOD(B2-A2,1)*24For a shift from 9:00 PM to 5:00 AM, the result is 8.
Format the result cell as a number rather than a time if you plan to use it as hours in a pay formula.
The *24 converts Excel's fraction of a day into hours.
For multiple shifts, calculate each shift's duration and sum the hours for the relevant workweek before applying the weekly overtime formula.
If the sheet includes unpaid breaks, subtract break duration from elapsed time.
For example, if break hours are in C2, use =MOD(B2-A2,1)*24-C2.
Handle pay periods and overtime rules carefully
Overtime thresholds depend on the applicable law, jurisdiction, employment agreement, and workweek definition.
Do not assume that a threshold applies per day or per pay period when the rule is weekly.
A two-week pay period may need separate weekly totals so overtime is not incorrectly offset between weeks.
For a timesheet with daily rows, first total each employee's hours by workweek.
You can use a PivotTable or a SUMIFS formula to sum hours for an employee and a date range.
Then apply the overtime calculation to each weekly total.
Make sure the workweek start and end dates match the organization's established payroll period.
Some rules use different multipliers, multiple overtime tiers, or special treatment for holidays and bonuses.
Store each rate or threshold in a clearly labeled cell and adapt the formula to the actual policy.
The examples here demonstrate spreadsheet mechanics and are not a determination of legal entitlement.
Round pay only when your policy requires it
Excel retains decimal precision even when a cell displays two decimal places.
This usually allows totals to be calculated from unrounded hours and rates, then displayed as currency.
If payroll policy requires rounding each person's pay to cents before summing, wrap the pay formula in ROUND:
=ROUND(D2*C2+E2*C2*$H$2,2)Choose a consistent rounding method and apply it at the required stage.
Rounding individual shifts, weekly hours, or final pay can produce different totals.
Check common errors
- Hours entered as clock time: Excel stores times as fractions of a day.
Convert elapsed time to hours with*24before multiplying by an hourly rate. - Negative overtime hours: Use
MAX(0,...)so employees below the threshold receive zero overtime hours. - Threshold changes when copying: Use absolute references such as
$H$1and$H$2. - Unexpected totals across a pay period: Group hours by the correct workweek before calculating overtime.
- Overnight shift displays incorrectly: Use
MODto account for the end time occurring after midnight, and format the calculated hours as a number.
The formulas shown use longstanding functions available in current desktop versions of Excel and Excel for the web, including Excel 2016 and later.
MIN, MAX, MOD, and ROUND are also available in Google Sheets, so the same examples generally transfer with the same cell references.