# Calculation Of Overtime

• I use Excel 2007, and I need help with an overtime calculator. It pertains to a specific wage order, which has three basic principles:

• Any hours over 16 in one day are double-time. (2x)
• Any hours over 40 in a week are time-and-a-half (1.5x)
• Any hours over 48 in a week are double-time. (2x)

I worked 5 hours on a Monday, 18 hours on a Tuesday, 18 hours on a Wednesday, and 13 hours on a Thursday. (I work in a residential group home, so 24 hour shifts are common). That totals 54 hours, and the correct overtime breakdown should be:

• 40 regular hours.
• 8 hours at time-and-a-half, and
• 6 hours of double time.

I’m using the following formulas:

• REG Hrs = IF(H2<=40,H2-U2,IF((H2-V2)-(U2-V2)<=40,(H2-V2)-(U2-V2),40))
• 1.5x Hrs = IF(H2<=40,0,IF((H2-40)<=8,MAX(0,(H2-40)-U2),IF((H2-40)>8,(H2-40)-U2,0)))
• 2x Hrs = IF(H2<=48,U2,IF(H2>48,U2,0))

H2 is the total hours worked for the week, which is 54; U2 is the total number of hours worked over 16 hours in one day, which is 4 (2 hrs on Tuesday and 2 hours on Wednesday); and V2 is the total number of hours over 40 for the entire week, which is 14. However, I’m getting the following results, which are wrong:

• 40 regular hours.
• 10 hours at time-and-a-half.
• 4 hours at double time.

I need to get 2 hours to switch from the 1.5x column to the 2x column. Can anyone help me? Thanks in advance.

• Re: Build Formula For Calculation Of Overtime

Try these
• 1.5x Hrs = IF(H2<=40,0,IF((H2-40)<=8,MAX(0,(H2-40)-MAX(H2-48,U2)),(H2-40)-MAX(H2-48,U2)))
• 2x Hrs = MAX(H2-48,U2)

• Re: Build Formula For Calculation Of Overtime

Instead of building “mega formulas” calculate one cell then use that cell value to calculate the next, etc.

Regular hours in cell B1
1 1/5 hours in cell B2
Double time ours in Cell B3

Cell B3 =IF((H2-U2)>48,H2-48,U2)

Cell B1 =IF(H2-B3>40,40,H2-B3)

Plug cell B2
Cell B2 =IF(H2-B1-B3>0,H2-B1-B3,0)

[FONT="Arial Black"][COLOR="blue"][SIZE="4"]Bill[/SIZE][/COLOR][/FONT]
Tip: To avoid chasing code always use Option Explicit.

• Re: Build Formula For Calculation Of Overtime

I think you could use the same formula that Brian suggested for 2x, i.e.

=MAX(U2,H2-48)

and for 1½x

=MAX(0,H2-MAX(U2,H2-48)-40)

edit2:forgot regular hours.......

=MIN(H2-U2,40)

Note: the above formulas work assuming U2 always shows a value (not a blank) so if there are no days where more than 16 hours are worked this should contain zero...

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!