+ Reply to Thread
Results 1 to 9 of 9

Timesheet template with 4 variable rates of pay

  1. #1
    Registered User
    Join Date
    07-20-2017
    Location
    London, England
    MS-Off Ver
    365
    Posts
    6

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

  2. #2
    Valued Forum Contributor leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    '97, 2016
    Posts
    1,737

    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. #3
    Registered User
    Join Date
    07-20-2017
    Location
    London, England
    MS-Off Ver
    365
    Posts
    6

    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. #4
    Valued Forum Contributor leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    '97, 2016
    Posts
    1,737

    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?
    Last edited by leelnich; 07-20-2017 at 03:56 PM.

  5. #5
    Registered User
    Join Date
    07-20-2017
    Location
    London, England
    MS-Off Ver
    365
    Posts
    6

    Re: Timesheet template with 4 variable rates of pay

    Quote Originally Posted by leelnich View Post
    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.

  6. #6
    Registered User
    Join Date
    07-20-2017
    Location
    London, England
    MS-Off Ver
    365
    Posts
    6

    Re: Timesheet template with 4 variable rates of pay

    anyone able to help or do you need more info?

  7. #7
    Registered User
    Join Date
    07-20-2017
    Location
    London, England
    MS-Off Ver
    365
    Posts
    6

    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))
    Attached Files Attached Files
    Last edited by DopeyDonkey; 07-27-2017 at 10:02 AM. Reason: added attatched and edited worksheet

  8. #8
    Forum Expert
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    4,173

    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.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  9. #9
    Registered User
    Join Date
    07-20-2017
    Location
    London, England
    MS-Off Ver
    365
    Posts
    6

    Re: Timesheet template with 4 variable rates of pay

    Quote Originally Posted by JeteMc View Post
    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1