# Timesheet template with 4 variable rates of pay

1. ## Timesheet template with 4 variable rates of pay

Hi guys,

I hope someone can help me, I'm trying to set up a timesheet which currently has 4 rates of pay depending on what hours of the day you work.

I work a 12 hr shift pattern at a normal rate of 14, but the overtime rate depends on what time of day it is. The following are the multipliers from the hourly rate.
 00:01 - 08:00 08:00 - 12:00 12:00 - 18:00 18:00 - 00:00 Mon 1.75 1.5 1.5 1.5 Tues -Thurs 1.5 1.5 1.5 1.5 Fri 1.5 1.5 1.5 1.75 Sat 1.75 1.75 2 2 Sun 2 2 2 2

If anyone can help me out here it would be amazing as I'm genuinely stuck...

2. ## Re: Timesheet template with 4 variable rates of pay

Hi DD, welcome to the forum! I'm not from GBR, what rules govern your OT? In my country, it's normally anything over 40 hrs/week.

3. ## Re: Timesheet template with 4 variable rates of pay

well we sign a waiver for the 40 hr week limit due to the nature of our job being 24/7 365 support. average week is 48 hrs, anything above is overtime.

4. ## Re: Timesheet template with 4 variable rates of pay

Questions:
1) Are hours tracked Monday 00:00 to Sunday 23:59?
2) If a shift runs into next week, are the hours split?
3) If overtime spans two rate periods, does each hour earn the corresponding rate?

5. ## Re: Timesheet template with 4 variable rates of pay

Originally Posted by leelnich
Questions:
1) Are hours tracked Monday 00:00 to Sunday 23:59?
2) If a shift runs into next week, are the hours split?
3) If overtime spans two rate periods, does each hour earn the corresponding rate?

1, Sat 00:00 - Friday 23:59
2, No
3, yes

i'm sure you can understand my frustration with it so far haha.

7. ## Re: Timesheet template with 4 variable rates of pay

Would something like this work (i do know that it wont work)?
I added a table to create the hourly rate it should be at.

 00:01 - 08:00 08:00 - 12:00 12:00 - 18:00 18:00 - 00:00 MON 24.5 21 21 21 TUE 21 21 21 21 WED 21 21 21 21 THU 21 21 21 21 FRI 21 21 21 24.5 SAT 24.5 24.5 28 28 SUN 28 28 28 28

=IF(C12<0,IF(A12=Sat,IF(F12=Y,E12*Data!N9,E12*Data!H12),\$E12*Data!N8),IF(A12=Sat,IF(F12=Y,E12*Data!P9,E12*Data!\$H\$12),E12*Data!\$H\$12))

8. ## Re: Timesheet template with 4 variable rates of pay

Looking at post #7 I assume that you would like to get a single formula solution, and perhaps someone will offer one in the future. I, however, will offer a proposed solution that employs several helper columns, which may be hidden for aesthetic purposes. For one thing I think that it will be easier to understand what each of the helper column formulas does and therefore easier to modify/correct the process should conditions change or if I have misunderstood the objective.
My apologies to my fellow contributors, however this solution contains such a variety of formulas that I don't think it would be very helpful to display them.
The overall strategy is to determine whether or not a shift has occurred after the 48 regular hours have been worked and then to calculate the pay based on whether the overtime hours were worked during the daytime or nighttime shift. That calculation utilizes the first/original table from the data sheet.
Formulas have only been copied as far as row 19 of the monthly time sheet, you'll need to select the range E19:V19 then drag the fill handle of V19 down to row 42.
I did some limited testing, as will be evident upon opening the file, however I would encourage rigorous testing.
Let us know if you have any questions or find any problems.

9. ## Re: Timesheet template with 4 variable rates of pay

Originally Posted by JeteMc
Looking at post #7 I assume that you would like to get a single formula solution, and perhaps someone will offer one in the future. I, however, will offer a proposed solution that employs several helper columns, which may be hidden for aesthetic purposes. For one thing I think that it will be easier to understand what each of the helper column formulas does and therefore easier to modify/correct the process should conditions change or if I have misunderstood the objective.
My apologies to my fellow contributors, however this solution contains such a variety of formulas that I don't think it would be very helpful to display them.
The overall strategy is to determine whether or not a shift has occurred after the 48 regular hours have been worked and then to calculate the pay based on whether the overtime hours were worked during the daytime or nighttime shift. That calculation utilizes the first/original table from the data sheet.
Formulas have only been copied as far as row 19 of the monthly time sheet, you'll need to select the range E19:V19 then drag the fill handle of V19 down to row 42.
I did some limited testing, as will be evident upon opening the file, however I would encourage rigorous testing.
Let us know if you have any questions or find any problems.
Thanks, i';ll have a look at the changes tonight plus I have a couple of months personal data I can enter to test :D

There are currently 1 users browsing this thread. (0 members and 1 guests)