Help on formula(s) for calculating overtime/doubletime in CA

Hello - thanks in advance for anyone taking the time to read over this.

Here are the criteria for overtime/doubletime in California:

If an employee works over 40 hours in a work week, then anything over 40 hours is paid overtime.
If an employee works over 8 hours in a day, then anything over the 8 hours (up to 12) is paid overtime.
If an employee works over 12 hours in a day, then anything over the 12 hours is paid doubletime.
If an employee works all 7 days within a pay period, then any hours (up to 8) worked on the 7th day are paid overtime.
If an employee works all 7 days within a pay period, then any hours over 8 worked on the 7th day are paid doubletime.

I've got the spreadsheet to set up where it's working in 99% of instances. The exception is when an employee works over 12 hours in one day, the formula correctly puts 4 hours in OT, and all hours over 12 in DT. However, if the employee works 9 hours the next day, it will not pick up the 1 hour of overtime as it should. This only happens when the employee works overtime on single days, but does not exceed 40 hours in a week.

On the spreadsheet, you can see that Joe's hours do not total correctly. However, if you change Bob's hours to 14 on any day, the formulas hold up since he is working over 40 hours in the week.

I attached the spreadsheet for anyone to look at and possibly tell me where I've gone wrong.

Thanks.

This saved my life. LOL I was losing my mind... You had the same issues I had and this fix worked. I am doing a payroll audit for my boyfriend and this sample sheet had exactly the formula I needed for his double time issue.

THANK YOU!