I need the right formula to work out how each shift is split, in hours, across standard pay and enhanced pay depending on the day of the week and the type of shift, e.g.;
DAY | SHIFT | TOTAL HOURS | STANDARD PAY | PAY +30% | PAY +60% |
MONDAY | EARLY | 7.5 | 7.5 | 0 | 0 |
TUESDAY | LATE | 7.5 | 6.5 | 1 | 0 |
WEDNESDAY | NIGHT | 10.5 | 0.5 | 10 | 0 |
SATURDAY | LATE | 7.5 | 0 | 0 | 7.5 |
SUNDAY | NIGHT | 10.5 | 0.5 | 7.5 | 2.5 |
So the staff member would add the day and the shift and the total hours while the standard, +30% and +60% columns would be worked out by the formula based on the day of the week and the shift columns. In other words - the combination of the day and the shift cells would determine the split of hours.
The KEY or parameters are set out in another part of the spreadsheet.
any help would be much appreciated