Need help making Excel distribute hours differently after working 40 hours

**paramedicdave** · 11-21-2009, 04:55 AM

Hello!

I live in California where employees are paid differently after working 40 hours in one week.

Hours 0 to 8 Regular time
Hours 9 to 12 Time and a half
Hours 13 and on Double-time

After working 40 hours in one week, the employee is then paid:

Hours 0 to 4 Time and a half
Hours 5 and on Double-time

I was able to make a spread sheet that would allow you to put in the amount of hours you worked and then would calculate regular, over time, and double-time automatically. What I need help with is making a formula that will then distribute the hours differently after working 40 hours in one week.

Can anyone help???? If needed I can email you a screenshot of the table I made.

Note:
C6 is the hours worked
H6 is regular time calculation =IF(C6<8,C6,8)
I6 is time and a half calculation =IF(C6>8,(IF(C6>12,4,C6-8)),"")
J6 is double time calculation =IF(C6>12,C6-12,"")

**teylyn** · 11-21-2009, 05:00 AM

So... what is the question? Your formulas seem to have it covered. What do you need help with?

You can upload a sample workbook here, no need for email addy exchange. Click "Go Advanced" below and then the paper clip icon to upload a file.

cheers

**paramedicdave** · 11-21-2009, 12:55 PM

What I need Excel to do is change the way it distributes the pay once the employee has worked over 40 hours in one week.

I uploaded two attachments. One is named "Wrong Pay Worksheet". This is as far as I could get.

The one that is named "Correct Pay Worksheet", I manually entered the numbers the way I want it to read. On line 9, 40 hours was reached for the week therefore the overtime hour distribution changed.

I hope this helps explain it and thanks for your help.