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.


    and for 1½x


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


    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!