# How to calculate overtime pay with IF in Excel

Pay regular hours at the base rate and the hours above 40 at 1.5 times: =IF(B2>$G$1,$G$1*C2+(B2-$G$1)*C2*$G$2,B2*C2).

**Excel:** `=IF(B2>$G$1,$G$1*C2+(B2-$G$1)*C2*$G$2,B2*C2)` (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 |  | Regular hours | 40 |
| Ana | 38 | 22 |  |  | Overtime factor | 1.5 |
| Ben | 45 | 20 |  |  |  |  |
| Cara | 40 | 25 |  |  |  |  |
| Dev | 52.5 | 18 |  |  |  |  |
| Eli | 41 | 30 |  |  |  |  |
| Fay | 36.5 | 24 |  |  |  |  |

Formula in D2, filled to D7: `=IF(B2>$G$1,$G$1*C2+(B2-$G$1)*C2*$G$2,B2*C2)`

Result: $836.00; $950.00; $1,000.00; $1,057.50; $1,245.00; $876.00

## How it works

When the hours are over 40, IF returns 40 hours at the base rate plus the extra hours at 1.5 times the rate; otherwise it returns hours times rate. Dev's 52.5 hours come to 40×18 + 12.5×18×1.5 = 1,057.50. Cara's 40 hours exactly earn no overtime.

## Other ways

- `=MIN(B2,$G$1)*C2+MAX(0,B2-$G$1)*C2*$G$2`: No IF: MIN caps the regular hours and MAX(0,…) gives the overtime hours, which are 0 for anyone at or under 40.
- `=B2*C2+MAX(0,B2-$G$1)*C2*($G$2-1)`: Pays every hour at the base rate, then adds the extra half for overtime hours.

## Common mistakes

- Hours entered as times (38:30) are fractions of a day. Convert them with `=B2*24` before applying the formula.
- Some rules apply overtime per day (over 8 hours) instead of per week. Use the same pattern on daily hours if so.

## Questions

**How do I show only the overtime hours?**

`=MAX(0,B2-$G$1)`.

**How do I add double time above 50 hours?**

Add a third slice: `=MIN(B2,40)*C2+MAX(0,MIN(B2,50)-40)*C2*1.5+MAX(0,B2-50)*C2*2`.

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-with-if