# Office 365 >  >  Timesheet template with 4 variable rates of pay

## DopeyDonkey

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...

----------


## leelnich

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.

----------


## DopeyDonkey

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.

----------


## 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?

----------


## DopeyDonkey

> 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.

----------


## DopeyDonkey

anyone able to help or do you need more info?

----------


## DopeyDonkey

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))

----------


## 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.

----------


## DopeyDonkey

> 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

----------

