CA Overtime Rules Timecard Calculator

HI THERE - I know what I want my spreadsheet to look like & what I want it to do, but I don't have the knowledge to create the formulas. A friend got me get this far - now stuck.

The CA Overtime rules are detailed on the spreadsheet.

Reg Hrs in D column should cap at 8 per day & 40 per week.
OT daily hrs 9 to 12 go to E column.
OT daily hrs 13+ go to column F.

Based on our workweek, emp is able to earn OT for working over 40 hrs in a week (this does not include OT hrs woked on a daily basis so as not to double dip) starting on Fri &/or Sat. These over 40 and over 48 hrs go to columns G & H.

7th Day rule wages also go in G & H.

Your help is appreciated - thank you.
Roxy[/SIZE]

Good Morning & thank you.

Hi There - I so appreciate your help and yes, it is quite complex.
To answer your question - "if you worked five 10 hour days Sunday through Friday you would actually be 2 hours over48 hours for the week, but that would not get paid at 2x correct?" You are correct because those 2 hrs (all 10 in your example) were already paid @ 1.5 as daily ot and the same hrs can't be paid at daily ot rate and then again at
weekly ot rate (double dip). The daily ot hrs over 8 "disappear" from the over 40 weekly ot calc. Weekly ot can only be earned beginning with the 41st reg hr for the week
which will be a FRI or SAT) OR on the 7th consecutive day of work regardless of the total hrs worked in the preceding 6 days.

I've attached your XLF CA OT (1) highlighting the problem areas. They're at the over 40 hrs per week & at the 7th day.
I've also attached the same sheet as XLF CA OT (2); I have manually input where & how the hrs should populate for reference.

THANK YOU