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.