# Shift Pay Calculations

#### paul_r26

##### New Member
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:

#### thisoldman

##### Well-known Member
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)))

#### paul_r26

##### New Member
Thank you very much, I’ll try tomorrow. ?

Last edited: