Setting formula to calculate ca overtime rules

**SmAsSilk** · 03-25-2013, 01:32 PM

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.

**Kevin UK** · 03-25-2013, 02:21 PM

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.

**SmAsSilk** · 03-25-2013, 02:37 PM

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.