# Setting formula to calculate ca overtime rules

1. ## Setting formula to calculate ca overtime rules

I'm having problems with getting the calculations right on this. The problem has been when the worker has less than 8 hrs in a day.........can someone help me with what I'm doing wrong on this Standard Timesheet (CA).xls? THANK YOU! Regular Time - =IF(SUM(MIN(K18,8),MIN(L18,8),MIN(M18,8),MIN(N18,8),MIN(O18,8),MIN(P18,8),MIN(Q18,8))>40,40,SUM(MIN(K18,8),MIN(L18,8),MIN(M18,8),MIN(N18,8),MIN(O18,8),MIN(P18,8),MIN(Q18,8)))
Over Time - =SUM(K18,L18,M18,N18,O18,P18,Q18,)-R15-R17
Double Time - =IF(K18>12,K18-12,0)+IF(L18>12,L18-12,0)+IF(M18>12,M18-12,0)+IF(N18>12,N18-12,0)+IF(O18>12,O18-12,0)+IF(P18>12,P18-12,0)+IF(Q18>12,Q18-12,0)

I'm wondering too if it could be due to the general layout of the timesheet format that we've create too that is "limiting" what we need.  Register To Reply

2. ## Re: Setting formula to calculate ca overtime rules

Hi SmAsSilk

Have a look at the attached file, enter your time as 17:00 hour or 05:00PM
Cells K18:018 are adding the hours per day.
R15 & S15 are recording the hours and O/T This is based on total time until 40 hours then any thing over is O/T
Any way have a look and see how you get on and were take it from there.  Register To Reply

3. ## Re: Setting formula to calculate ca overtime rules

What I need to figure out is how to get the Regular time hours to reflect that the worker actually worked 39 hrs regular time and his OT should be six hours. That's where the error is.  Register To Reply

4. ## Re: Setting formula to calculate ca overtime rules

SmAsSilk

You need to sort your sheet out first, so it will not incur those unnecessary long formulas! That is what I started to do there.  Register To Reply

5. ## Re: Setting formula to calculate ca overtime rules

ok thanks..........  Register To Reply