+ Reply to Thread
Results 1 to 15 of 15

Calculating multiple pay rates based on time of day worked, not number of hours worked

  1. #1
    Registered User
    Join Date
    03-01-2020
    Location
    USA
    MS-Off Ver
    365
    Posts
    11

    Calculating multiple pay rates based on time of day worked, not number of hours worked

    Trying to come up with a formula that will calculate 3 pay rates that are based on the time of day, not the number of hours worked.

    For example:
    $10/hr for 7a-7p
    $11/hr for 7p-12a
    $12/hr for 12a-7a

    If the person worked 2 hours from 9-11 am, they would get $11x2 hours

    If the person worked 14 hours from 6 pm to 8 am the next day, they would get:
    $10 for 6p-7p
    $55 for 7p-12a
    $84 for 12a-7a
    $10 for 7a-8a
    ===
    $159 total


    Thanks for any advice.

    I have formulas like D2-TIMEVALUE("18:00") but that doesn't take into account if the time period started after the start of Rate 2, and would return only the difference between the time and 18:00 instead of the start time, say at 18:30.
    Attached Files Attached Files
    Last edited by vdbonce; 03-01-2020 at 04:05 AM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Calculating multiple pay rates based on time of day worked, not number of hours worked

    Welcome to the forum!

    What we have here is a case of very poor data layout, so my first question is can it be changed?
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    03-01-2020
    Location
    USA
    MS-Off Ver
    365
    Posts
    11
    Thanks for replying! The poor spacing is due to making notes for the manual calculations but if you think the overall layout can be improved I’d be happy to take recommendations.
    Last edited by AliGW; 03-01-2020 at 05:31 AM. Reason: Please don't quote unnecessarily!

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Calculating multiple pay rates based on time of day worked, not number of hours worked

    It's not just the spacing - it's the way you have chosen to represent data. This lookup table, for instance, is the stuff of nightmares:

    Excel 2016 (Windows) 32 bit
    C
    D
    E
    F
    1
    HOURS
    RATE 1
    RATE 2
    RATE 3
    2
    $10/hr $11/hr $12/hr
    3
    7a-7p 7p-12a 12a-7a
    Sheet: Sheet1

    Are you open to suggestions here? What you should be using is one type of data (time/date/monetary value) in each cell, not a mix thereof.

    This would be better:

    Excel 2016 (Windows) 32 bit
    J
    K
    L
    2
    00:00:00
    Rate 3
    $ 12.00
    3
    07:00:00
    Rate 1
    $ 10.00
    4
    19:00:00
    Rate 2
    $ 11.00
    Sheet: Sheet1

    Or even better:

    Excel 2016 (Windows) 32 bit
    J
    K
    L
    M
    1
    Start
    End
    Rate
    Pay
    2
    00:00:00
    06:59:59
    Rate 3
    $ 12.00
    3
    07:00:00
    18:59:59
    Rate 1
    $ 10.00
    4
    19:00:00
    23:59:59
    Rate 2
    $ 11.00
    Sheet: Sheet1
    Last edited by AliGW; 03-01-2020 at 05:45 AM.

  5. #5
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Calculating multiple pay rates based on time of day worked, not number of hours worked

    PL see file. I have put 2 tables. One for tariff J1:M3. Other one for schudles and calculation in A1.
    K2:M3 formatted for [h]:mm
    IN D2 then copied across
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by kvsrinivasamurthy; 03-01-2020 at 08:22 AM.
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  6. #6
    Registered User
    Join Date
    03-01-2020
    Location
    USA
    MS-Off Ver
    365
    Posts
    11

    Re: Calculating multiple pay rates based on time of day worked, not number of hours worked

    Thank you both so much! That was really helpful! Sorry for the messy sample document earlier, as I was trying to simplify the worksheet to highlight my question.

    I incorporated your suggested formulas into a bigger worksheet, and for the most part it is doing what I want, but now I am trying to add in a 4th rate, and it seems that having the rate be applicable to a time period crossing midnight is causing problems. Would adding a 1 somewhere in the formula fix this? Would appreciate any pointers.

    The goal is to have the following data entered and then everything else gets calculated:
    date
    start
    stop
    pre - time tacked on prior to start
    post - time tacked on after stop
    extra - extra time added to pay at rate 1


    Notes:
    - Not sure why the error check is showing discrepancy in N5 and Q5.

    Thanks again!
    Attached Files Attached Files
    Last edited by vdbonce; 03-02-2020 at 02:12 AM. Reason: updating excel sample

  7. #7
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Calculating multiple pay rates based on time of day worked, not number of hours worked

    22:00 to 7:00 hrs is only for Sat and sun or for all days. explain clearly what are timings for all days.

  8. #8
    Registered User
    Join Date
    03-01-2020
    Location
    USA
    MS-Off Ver
    365
    Posts
    11

    Re: Calculating multiple pay rates based on time of day worked, not number of hours worked

    Sorry for the confusion.

    RATE 1: Weekdays 0700-1800 + all of Column H (labeled "extra") regardless of day of week
    RATE 2: Weekdays 1800-2000
    RATE 3: Weekdays 2000-2200
    RATE 4: Weekdays 2200-0700 and all day Sat/Sun

    I added the following to RATE4 calculations to account for the Fri/Sat and Sun/Mon overlaps:
    +IF(AND(A2="Fri",J2<I2,J2>$AF$3),J2-$AF$3,0)
    -IF(AND(A2="Sun",J2<I2,J2>$AF$3),J2-$AF$3,0)

    The problem now seems to be that when I set RATE 4 to start at 2200 through 0700 instead of 0000 to 0700 in the initial solution, the equation (IF($B2<$A2,MAX(0,MIN(K$3,1)-MAX(K$2,$A2))+MAX(0,MIN(K$3,$B2)-MAX(K$2,0)),MAX(0,MIN(K$3,$B2)-MAX(K$2,$A2)))) no longer works, and returns 0. This is Column U. The manually calculated values that should be in Column U are in Column V.

    Thanks for any insight!
    Last edited by vdbonce; 03-02-2020 at 03:56 AM.

  9. #9
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Calculating multiple pay rates based on time of day worked, not number of hours worked

    In V10 how it is 165/-

  10. #10
    Registered User
    Join Date
    03-01-2020
    Location
    USA
    MS-Off Ver
    365
    Posts
    11

    Re: Calculating multiple pay rates based on time of day worked, not number of hours worked

    RATE4 is $15, and since the pay period is Friday night 21:00 into Saturday morning 9:00, this would be 1 hour at RATE 3 ($12) and 11 hours at RATE 4 ($15).

    Cell V10 is the RATE4 manual calculation, which is 11*$15 = $165.

    Cell U10 is showing $30 because of the "+IF(AND(A2="Fri",J2<I2,J2>$AF$3),J2-$AF$3,0)" I added which accounts for the period of 0700-0900 Saturday since the original equation wouldn't capture that. So U10 is missing Friday 2200 to Saturday 0700.

    It seems to be this part of the formula that is returning 0 now.
    "IF($J2<$I2,MAX(0,MIN(AI$4,1)-MAX(AI$3,$I2))+MAX(0,MIN(AI$4,$J2)-MAX(AI$3,0)),MAX(0,MIN(AI$4,$J2)-MAX(AI$3,$I2))"

    Column AD has my manual calculations.

    Thanks again!
    Last edited by vdbonce; 03-02-2020 at 04:37 AM. Reason: fixed typo regarding missing time from Friday 2200

  11. #11
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Calculating multiple pay rates based on time of day worked, not number of hours worked

    Pl see file. Thorolly check all functions for all conditionsfor all days of week before implementing.
    In L2 then copy down
    Please Login or Register  to view this content.
    In U2 then copy down
    Please Login or Register  to view this content.
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    03-01-2020
    Location
    USA
    MS-Off Ver
    365
    Posts
    11

    Re: Calculating multiple pay rates based on time of day worked, not number of hours worked

    Thank you!! That seems to work! Playing around with the cells, I also tried this (because I understand its logic better):

    L2:
    =AF$2*24*(IF(OR($A2="Sat",$A2="Sun"),0,IF($J2<$I2,MAX(0,MIN(AF$4,1)-MAX(AF$3,$I2))+MAX(0,MIN(AF$4,$J2)-MAX(AF$3,0)),MAX(0,MIN(AF$4,$J2)-MAX(AF$3,$I2))))
    +IF(AND($A2="Sun",$J2<$I2,$J2>$AF$3),$J2-$AF$3,0)
    -IF(AND($A2="Fri",$J2<$I2,$J2>$AF$3),$J2-$AF$3,0)
    +$H2)

    This means:

    if Sat/Sun, then RATE 1 = 0 hours, otherwise on weekdays calculate based on the formula you had
    +if Sun->Mon, add the Monday time after 0700
    -if Fri->Sat subtract the Saturday time after 0700
    +column H (extra added time)



    U2:
    =AI$2*24*(IF(OR($A2="Sat",$A2="Sun"),0,IF($J2<$I2,MAX(0,MIN(AI$4,1)-MAX(AI$3,$I2))+MAX(0,MIN(AI$4,$J2)-MAX(AI$3,0)),MAX(0,MIN(AI$4,$J2)-MAX(AI$3,$I2)))))
    +
    AJ$2*24*(IF(OR($A2="Sat",$A2="Sun"),IF(J2>=I2,(J2-I2),(J2+1-I2)),IF($J2<$I2,MAX(0,MIN(AJ$4,1)-MAX(AJ$3,$I2))+MAX(0,MIN(AJ$4,$J2)-MAX(AJ$3,0)),MAX(0,MIN(AJ$4,$J2)-MAX(AJ$3,$I2))))
    +IF(AND($A2="Fri",$J2<$I2,$J2>$AF$3),$J2-$AF$3,0)
    -IF(AND($A2="Sun",$J2<$I2,$J2>$AF$3),$J2-$AF$3,0))

    This means:

    Weekday 2200-2400 calculation based on your formula
    +if weekend, all day, and if not weekend, 0000-0700 calculation based on your formula
    -if Sun->Mon, subtract the Monday time after 0700
    +if Fri->Sat, add the Saturday time after 0700

    This also seems to work?

    Do you see any traps or potential errors between the one I came up with and the one you came up with?

    Thanks again!
    Last edited by vdbonce; 03-03-2020 at 01:53 PM.

  13. #13
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Calculating multiple pay rates based on time of day worked, not number of hours worked

    As per data in table it seems there is no problem. any way it is better to check with some more different data.

  14. #14
    Registered User
    Join Date
    03-01-2020
    Location
    USA
    MS-Off Ver
    365
    Posts
    11

    Re: Calculating multiple pay rates based on time of day worked, not number of hours worked

    Thank you very much!

  15. #15
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Calculating multiple pay rates based on time of day worked, not number of hours worked

    Welcome. Have a good day.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 8
    Last Post: 09-30-2017, 07:00 PM
  2. Replies: 0
    Last Post: 05-14-2012, 05:36 PM
  3. Calculating basic hours worked, between a time range and premium hours worked
    By RoyLittle0 in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 01-07-2012, 06:59 AM
  4. Replies: 0
    Last Post: 01-05-2012, 06:23 AM
  5. Calculating wages from hours worked and hourly rates?
    By mathrocks in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 07-13-2010, 03:22 AM
  6. Replies: 0
    Last Post: 08-23-2005, 12:33 PM
  7. Replies: 0
    Last Post: 08-23-2005, 12:32 PM

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