Hello!
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.
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!
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!
