Calculating Overtime Premium

Hi,

I have developed a spreadsheet to calculate wages, and produce an upload into sage. This sheet will save me a day and a half in manual paper processing, but I am unable to calculate overtime premium worked on Saturdays.

At present, the first 4 hours over 37.5 are paid at time and one third, the next 4 hours, time and one half, and anything over that is double time.

The problem occurs if they work Saturdays. They automatically go onto time and one half for the first six hours, and then the hours are distributed as illustrated below.

1/3--1/2--DBL---TOTAL

0-----6-----0-----6
1-----6-----0-----7
2-----6-----0-----8
3-----5-----1-----9
4-----4-----2-----10
4-----4-----3-----11


I am only able to get overtime premium to work in multiples of 4.
If anyone could offer some solution, this would be greatly appreciated

Thanks

Sue

Hi Cutter,

The cells which contain upload data are

V705 to V770

Thanks

Sue

My excel skills may be somewhat limited, but I have managed to get the upload to split out the overtime premium payable based on 1/3, 1/2, and double time as required by the company.

The upload has been tested in sage and works perfect.

My problem now is finding a way to get excel to recognise that if someone works on saturday, then the rules change.

Unfortunatly, I can't get it to work, and maybe I need to approach the problem differently.

Any advice, better still a solution would be greatly appreciated

Sue