California Overtime Formula Help!!!

[sam.farley]

So I have a spreadsheet put together and I thought it was all figured out...but then I noticed something and I can't find the solution...

In California...OT is calculated at anything over 8 hours in a day, anything over 40 regular hours in the week, or any hours worked on the 7th consecutive day in the pay week. Double time is based on anything over 12 hours each day or anything over 8 hours on the 7th consecutive day.

So the spreadsheet i have seems to be computing all of that...except in cell I8 or I17...the overtime is not calculating. Normally would not be a problem...but our pay week is Sunday - Saturday and I have one employee that works a short shift on Sunday and sometimes goes into overtime on Friday...and I8 or I17 is not moving the OT over...the hours seem to just disappear and I cannot figure out how to fix it.

Any help would be appreciated!!! thanks!

Timecard Master.xlsx

[rcm]

Please Login or Register  to view this content.

[sam.farley]

Thank you so much! that seems to help and should work. The only thing now is that if someone worked 7 straight days at 8 hours each day...on the 6th day hours worked shows 8 hours, but OT shows 12 hours. This is RARE that this would ever happen...but something to look at. My brain is swimming...seems the answer should be easy but I can't figure it out. but thank you so much for helping!!!

[JeteMc]

The only thing now is that if someone worked 7 straight days at 8 hours each day...on the 6th day hours worked shows 8 hours, but OT shows 12 hours. This is RARE that this would ever happen...but something to look at.

I worked at this from another direction using the following formula to calculate the regular hours:

Please Login or Register  to view this content.

Then the following formula for double overtime, as that is perhaps the easier of the two overtimes:

Please Login or Register  to view this content.

I then simply let the overtime column populate by subtracting the regular and double overtime from the actual, like so:

Please Login or Register  to view this content.

Note that due to these changes if an employee works seven 8 hour days the sixth day will show 8 hours of overtime and everything else seems to work as before. The formulas are applied to the attached copy of your file on sheet1 (2):
Copy of Timecard Master.xlsx
Let me know if you have any questions.

[Alenarvaez8]

Thank you for this!!