I am trying to come up with a spreadsheet that calculates California OT. CA is very different compared to all the other states. You must calculate Monday through Sunday so that anything after 40 hours in the week TOTAL is all overtime, then each day under the 40hrs must be <8hrs is regular time, 8-12 hours is 1 1/2 OT and anything over 12 hours is 2x OT.........but if employee works 7 consecutive days in a row, on Sunday (being the 7th day) they get 2x OT for all hours worked... Please help.
EX:
Mon 12hrs = 8 hrs regular, 4 hrs -1 1/2
Tues 18hrs = 8 hrs reg, 4 hrs -1 1/2, 6 hrs -2x
Wed 15hrs = 8 hrs regular, 7 hrs -1 1/2--------> this makes it 45hrs today, so anything over the 40 hours worked today is 1 1/2 OT
Thurs 17hrs = all hours <12hrs are- 1 1/2 OT and >12hrs are -2x OT
Fri 8hrs = all hours are 1 1/2
Sat 4hrs = all hours are 1 1/2
Sun 6hrs = all hours are 2x

regular hours = 24
1 1/2 OT= 39
2x OT = 17

Hello,

welcome to UA!

You provided very detailed explanation, especially to us non-californians , but still there are some questions (they may be due to my poor knowledge of U.S. legislative, for sure)

Couple of IFs combined with - probably - some array math should handle this issue, but can you please elabrorate a bit more on this:

Wednesday - You reached 40 hours and worked 5 more, so why is seven hours 1 1/2 OT?

Do the rates cummulate? I mean, if you worked 60 hours in the week, you get 20 as an OT, but if you worked also 7 days in a row, you get extra OT for Sunday as well?

Try to make an example in an Excel sheet, zip it and attach (browse for it on your HDD when you click 'Continue' when replying)

Martin

Hey,

For Wednesday.... after 40 hours.... anything for the rest of the day after the first 8 are only 1 1/2 OT..... CA has to be the only difficult state.

CA rates...... go 40 hours in the week total...... but it depends on how you worked the 60 hours...... they calculate a work week as 40 hours in a 7 day period, but if they work 7 days in a row, they get all hours on Sunday at 2x OT.

I will have an excel sheet attached so you can see.

More clear now, but not that trivial as I thought...

I will have a look a bit later, going off-line now

regards
Martin

Not trivial at all. I did a time sheet for the California employees at my wife's company a couple of years ago. I ended up spending about 7 or 8 hours on it. The reason it was so difficult was that the paid on the 15th and the last day of the month. That 7th day coul fall anywhere in that period.

I am willing to share a copy of that workbook, as long as you check and double check the calculations. I'm sure it could be cleaned up a bit, but I was so sick of the thing when I finished I left it as is. The password for sheet protection and the VBA password are both "sooner".

Thank you very much. The site will not let me download the attachment.