Shift Pay Calculations

paul_r26

New Member
Joined
May 25, 2010
Messages
7
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
Joined
Jan 5, 2014
Messages
1,073
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)))
 

Forum statistics

Threads
1,086,087
Messages
5,387,726
Members
402,075
Latest member
COwen

Some videos you may like

This Week's Hot Topics

Top