How to calculate overtime pay over 40 hours in Excel

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

Q: How do I get the hours from clock times?

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

Formula (Excel and Google Sheets)

=MIN(B2,$G$1)*C2+MAX(0,B2-$G$1)*C2*$G$2

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.

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

See it work

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

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.

=MIN(B2,$G$1)*C2+MAX(0,B2-$G$1)*C2*$G$2

Result946

B242
The value in B2.
$G$140
The value in G1.
MIN(B2,$G$1)40
MIN: Smallest number.
- number1B242
- number2$G$140
C2$22.00
The value in C2.
MIN(B2,$G$1)*C2880
The * operator multiplies.
B2-$G$12
The - operator subtracts.
MAX(0,B2-$G$1)2
MAX: Largest number.
- number10
- number2B2-$G$12
MAX(0,B2-$G$1)*C244
The * operator multiplies.
$G$21.5
The value in G2.
MAX(0,B2-$G$1)*C2*$G$266
The * operator multiplies.
MIN(B2,$G$1)*C2+MAX(0,B2-$G$1)*C2*$G$2946
The + operator adds.

In Google Sheets

The same formula works in Google Sheets: =MIN(B2,$G$1)*C2+MAX(0,B2-$G$1)*C2*$G$2. 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
=B2C2+MAX(0,B2-$G$1)C2*($G$2-1)	$946.00, $741.00, $1,281.25…	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.

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 over 40 hours

In D2, calculate the week's pay: hours up to G1 at the rate, and hours over it at the rate times G2. Fill down to D6.

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.

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.