# How to calculate overtime pay over 40 hours in Excel

Pay the first 40 hours at the rate and the rest at 1.5 times: `=MIN(B2,40)*C2+MAX(0,B2-40)*C2*1.5`, with 40 and 1.5 in cells.

**Excel:** `=MIN(B2,$G$1)*C2+MAX(0,B2-$G$1)*C2*$G$2` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G |
| --- | --- | --- | --- | --- | --- | --- |
| Employee | Hours | Rate | Pay |  | Overtime after (hours) | 40 |
| Ana | 42 | 22 |  |  | Overtime multiplier | 1.5 |
| Ben | 38 | 19.5 |  |  |  |  |
| Cara | 47.5 | 25 |  |  |  |  |
| Dev | 40 | 30 |  |  |  |  |
| Eli | 51 | 18 |  |  |  |  |

Formula in D2, filled to D6: `=MIN(B2,$G$1)*C2+MAX(0,B2-$G$1)*C2*$G$2`

Result: $946.00; $741.00; $1,281.25; $1,200.00; $1,017.00

## How it works

`MIN(B2,$G$1)` is the regular hours, paid at the base rate, and `MAX(0,B2-$G$1)` the overtime hours, paid at 1.5 times the rate. Ana worked 42 hours: 40 × $22 plus 2 × $33, or $946.

## Other ways

- `=B2*C2+MAX(0,B2-$G$1)*C2*($G$2-1)`: Pays every hour at the base rate, then adds the overtime premium on top.

## Common mistakes

- If the hours are stored as a time (42:00), convert them first with `B2*24`.
- Keep the threshold and the multiplier in cells. Rules differ by country, state and contract.

## Questions

**How do I add double time over 12 hours in a day?**

Add a third tier on top of time and a half: `+MAX(0,hours-12)*rate*0.5`.

**How do I get the hours from clock times?**

Subtract, multiply by 24 and take off breaks: `=(out-in)*24-break_hours`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/overtime-pay-over-40-hours