How to calculate overtime pay with IF in Excel

Q: 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.

Formula (Excel and Google Sheets)

=IF(B2>$G$1,$G$1*C2+(B2-$G$1)*C2*$G$2,B2*C2)

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).

Result on the example: one result per row in D2, filled down to D7
Works in: Every current version
Google Sheets: Same formula

See it work

The formula is already in D2, filled down to D7. Change any input and the result updates, or edit the formula to experiment.

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.

=IF(B2>$G$1,$G$1*C2+(B2-$G$1)*C2*$G$2,B2*C2)

Result836

B238
The value in B2.
$G$140
The value in G1.
B2>$G$1FALSE
The > operator tests greater than.
C2$22.00
The value in C2.
$G$1*C2880
The * operator multiplies.
B2-$G$1-2
The - operator subtracts.
B2-$G$1)*C2-44
The * operator multiplies.
$G$21.5
The value in G2.
B2-$G$1)*C2*$G$2-66
The * operator multiplies.
$G$1*C2+(B2-$G$1)*C2*$G$2814not used
The + operator adds.
B2*C2836
The * operator multiplies.
IF(B2>$G$1,$G$1*C2+(B2-$G$1)*C2*$G$2,B2*C2)836
IF: Returns one value if a test is true, another if false.
- logical_testB2>$G$1FALSE
- value_if_true$G$1*C2+(B2-$G$1)*C2*$G$2814not used
- value_if_falseB2*C2836

In Google Sheets

The same formula works in Google Sheets: =IF(B2>$G$1,$G$1*C2+(B2-$G$1)*C2*$G$2,B2*C2). We ran it in Google Sheets mode on the same data and it returns the same results.

Other ways to do it

Each one was run on the same example sheet.

Formula	Result	When to use it
=MIN(B2,$G$1)C2+MAX(0,B2-$G$1)C2*$G$2	$836.00, $950.00, $1,000.00…	No IF: MIN caps the regular hours and MAX(0,…) gives the overtime hours, which are 0 for anyone at or under 40.
=B2C2+MAX(0,B2-$G$1)C2*($G$2-1)	$836.00, $950.00, $1,000.00…	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.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Calculate overtime pay with IF

In D2, calculate the week's pay: hours up to G1 at the rate, and hours above it at the rate times the factor in G2. Fill down to D7.

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.

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.