I would like some help from you Excel experts.

I have a staff rota / schedule, I have already included a formula which will calculate the total length of a shift using the formula:

=(IF(B2>C2,C2+1-B2,C2-B2))*24

This works great, however we have two different rates of pay depending on the times worked so day rate is hours worked between 05:00 and midnight, night rate is hours worked between midnight and 05:00. I would now like to automatically calculate how many hours were at day rate and how many hours were at night rate.

Next I would like to calculate the break entitlement based on the following rules which depend on the total number of hours worked, these rules are as follows:

4 hours or more but <6 hours = 15 minutes

6 hours or more but <8 hours = 30 minutes

8 hours or more but <9 hours = 45 minutes

9 hours or more but <11 hours = 1 hour

11 hours or more = 90 minutes

I have created a quick example (attached) of what I would like to achieve, the cells in white are the data I would enter (staff member name and their shift start and finish times), green cells already calculate as I would like and yellow cells are those which I could do with some help with.

Example Rota.xlsx | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|

A | B | C | D | E | F | G | H | |||

1 | Staff | Start | Finish | Total Hours | Total Night Hours Between Midnight & 05:00 | Total Day Hours Between 05:00 & Midnight | Break Entitlement (Based On Total Hours) | Total Paid Hours | ||

2 | Staff Member 1 | 20:00 | 07:00 | 11 | 5 | 6 | 1.5 | 9.5 | ||

3 | Staff Member 2 | 23:30 | 06:00 | 6.5 | 5 | 1.5 | 0.5 | 6 | ||

4 | Staff Member 3 | 20:00 | 08:00 | 12 | 5 | 7 | 1.5 | 10.5 | ||

5 | Staff Member 4 | 08:00 | 17:00 | 9 | 0 | 9 | 1 | 8 | ||

6 | ||||||||||

7 | 4 hours or more but <6 hours = 15 minutes 6 hours or more but <8 hours = 30 minutes 8 hours or more but <9 hours = 45 minutes 9 hours or more but <11 hours = 1 hour 11 hours or more = 90 minutes | |||||||||

Sheet1 |

Cell Formulas | ||
---|---|---|

Range | Formula | |

D2:D5 | D2 | =(IF(B2>C2,C2+1-B2,C2-B2))*24 |

H2:H5 | H2 | =SUM(D2-G2) |

Not sure how easily achievable the above is, I appreciate I may have made it more difficult by trying to do two different calculations so if you can only help with one then that is fine.

I would really appreciate any help I can get.

Thank you!