Spreadsheet to calculate overtime and total hours

[mccartneyd1795]

Hi I am needing help to create a formula within my timesheet tracking spreadsheet to track the number of hours my team have worked during a working week to assist our accounts department to ensure they are receiving the correct pay.

Our working week is Monday to Friday and we work 8 hours per day, therefor they work for 40 hours a week. Often they can work overtime mid week and on a Saturday and Sunday. Overtime is calculated at time and a half, except from a Sunday where it is double time.

Because of this we have a rule where you cannot work a Sunday if you haven't worked a Saturday and you only get overtime once you have worked your required 40 hours.

As you will see in my attached test spreadsheet below I am having issues with the columns S, T, and U. I need the hours to go to 40 first in the column S, then fill out the columns T and U if affected by overtime.

Sorry if this is hard to understand, difficult to put it into words haha. Any help is much appreciated. Thanks.

This i the formula I am using for S - =IF((O3+P3+Q3)>=40,40,(O3+P3+Q3))
and the formula I am using for T - =IF((O3+P3+Q3)>=40,((O3+P3)-40),0)

I am not sure if these are correct, but I am having issues with column U and the implementation of our works rules.

Many thanks for any help.

 

[HyraxWithAFlamethrower]

For S, =MIN(40, SUM(B3:L3)) That will take care of any issues where they work fewer hours during the week, but then work a long day or Saturday if still under 40 hours.
For T, =MAX(0,SUM(BL3:L3)-40)
For U, =IF(L3>0,M3,0)
Hope this helps!