Results 1 to 3 of 3

Thread: Shift Pay Calculations
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    May 2010
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Shift Pay Calculations

    Need some help please; I'm working on a cost comparison and need some help with a sum that involve shift length, different rates depending on the time of day and the start time of the shift.



    E.g.
    - If an eployee starts at 04:00 set in column C, I would like the sum to pay the correct "04:00-18:00" rate of pay, for the hours in the "shift length" cell. (11 hours at H4)

    - If an employee starts at 12:00, I would like the sum pay them the correct proportion of rates. (11 hours shift as per J4, 6 hours at the rate in H4 and 5 hours at H5)

    - If an employee starts at 22:00, I would like the sum to pay them the rate for the shift length in J4, however once an employee is paid a higher rate it cannot drop even if they work after 04:00. (11 hours at H5 regardless that they will be working after 04:00)

    Thank you very much for any assistance.
    Last edited by paul_r26; Jun 9th, 2018 at 04:44 AM.

  2. #2
    Board Regular
    Join Date
    Jan 2014
    Location
    Pittsburgh
    Posts
    1,073
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Shift Pay Calculations

    Try this in E4 and copy downward.

    =IF(OR(HOUR(C4)>=18, HOUR(C4)< 4), $H$5*$J$4,
    IF(AND(HOUR(C4)>=4, HOUR(C4)+$J$4<=18), $H$4*$J$4,
    $H$4*(18-HOUR(C4)) + $H$5*($J$4+HOUR(C4)-18)))

  3. #3
    New Member
    Join Date
    May 2010
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Shift Pay Calculations

    Thank you very much, Iíll try tomorrow. 👍
    Last edited by paul_r26; Jun 10th, 2018 at 02:36 PM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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