Payroll calculator Help need (3 different rates of pay based on time worked)

[Jim Deans]

Hi,
Have searched through, i cant seem to find quite the same problem so any help would be much appreciated.

I am trying to put together a simple pay calculator but i cant quite work out the functions for the 3 variables. I did try and use Excel and payroll calculations for shifts as a basis but my VBA skills are lacking (to say the least) so whilst i could get the basics of the code, i couldnt really adjust it to include 3 different rates (as opposed to just the 1). As a description, hours worked on friday into saturday are at friday rate untill 00:00 on sat morning, then the remaining would be at sat rates, if hours worked on sunday run into monday, then the whole period would be paid at sunday rates.

Any help would be appreciated.

Cheers, Jim
Excel 2007

	A	B	C	D	E	F	G	H	I	J	K
Lower Limit	Upper Limit
Saturday Rate
Sunday Rate

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Start Date[/TD]
[TD="align: center"]Day[/TD]
[TD="align: center"]Start Time[/TD]
[TD="align: center"]Lunch[/TD]
[TD="align: center"]Finish Time[/TD]
[TD="align: center"]Total Hours[/TD]
[TD="align: center"]Worked Hours[/TD]
[TD="align: center"]Standard Rate Hours[/TD]
[TD="align: center"]Late Rate Hours[/TD]
[TD="align: center"]Night Rate Hours[/TD]
[TD="align: center"]Total Pay[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]01/01/2013[/TD]
[TD="align: right"]Tue[/TD]
[TD="align: right"]06:30[/TD]
[TD="align: right"]00:45[/TD]
[TD="align: right"]17:45[/TD]
[TD="align: right"]11.25[/TD]
[TD="align: right"]10.5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]£0.00[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]13/01/2013[/TD]
[TD="align: right"]Sun[/TD]
[TD="align: right"]22:00[/TD]
[TD="align: right"]00:45[/TD]
[TD="align: right"]05:00[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]6.25[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]£0.00[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]12/01/2013[/TD]
[TD="align: right"]Sat[/TD]
[TD="align: right"]21:00[/TD]
[TD="align: right"]00:45[/TD]
[TD="align: right"]20:00[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]22.25[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]£0.00[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]

[TD="align: center"]Pay[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]Standard Rates[/TD]
[TD="align: right"]06:30[/TD]
[TD="align: right"]18:29[/TD]
[TD="align: center"]£10.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]Late Rate[/TD]
[TD="align: right"]18:30[/TD]
[TD="align: right"]00:29[/TD]
[TD="align: center"]£11.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]Night Rate[/TD]
[TD="align: right"]00:30[/TD]
[TD="align: right"]06:29[/TD]
[TD="align: center"]£12.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]00:00[/TD]
[TD="align: right"]23:59[/TD]
[TD="align: center"]£13.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]00:00[/TD]
[TD="align: right"]23:59[/TD]
[TD="align: center"]£14.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​