Forum Discussion
Formula calculating earnings during one shift when three different rates of pay apply
Start | End | Hours | Earnings |
| 10:00:00 | 20:00:00 | 10:00:00 | |
| 08:00:00 | 18:00:00 | 10:00:00 | |
| 15:00:00 | 00:00:00 | 09:00:00 | |
| 10:00:00 | 20:00:00 | 10:00:00 | |
| 10:00:00 | 20:00:00 | 10:00:00 | |
| 14:00:00 | 22:00:00 | 08:00:00 | |
| 14:00:00 | 22:00:00 | 08:00:00 | |
| 23:00:00 | 08:30:00 | 09:30:00 | |
| 10:00:00 | 20:00:00 | 10:00:00 | |
| 10:00:00 | 20:00:00 | 10:00:00 | |
| 22:00:00 | 08:30:00 | 10:30:00 | |
| 22:00:00 | 08:30:00 | 10:30:00 |
The rates of pay are:
| Rate of Pay | Start | End |
| £10 | 07:00:00 | 19:00:00 |
| £15 | 19:00:00 | 22:00:00 |
| £20 | 22:00:00 | 07:00:00 |
So for instance, if someone worked 15:00:00 - 00:00:00 they would be paid £125:
- 15:00:00 - 19:00:00 @£10/hr = £40
- 19:00:00 - 22:00:00 @£15/hr = £45
- 22:00:00 - 00:00:00 @£20/hr = £40
I've managed to come up with a formula that will calculate number of hours worked:
=IF(AC19>AB19,AC19-AB19,1-AB19+AC19)
I need a formula that will tell me how much is earned during each shift.
Many thanks.
First I'd define rates for this and next days as
Next day is in bold here.
With that formula could be
=SUMPRODUCT( IF($B3>$H$3:$H$7,0, IF($G$3:$G$7>($C3+1*($C3<$B3)), 0, IF(($C3+1*($C3<$B3))>$H$3:$H$7,$H$3:$H$7,($C3+1*($C3<$B3)))- IF($B3>$G$3:$G$7,$B3,$G$3:$G$7) ) )*$I$3:$I$7)*24
4 Replies
First I'd define rates for this and next days as
Next day is in bold here.
With that formula could be
=SUMPRODUCT( IF($B3>$H$3:$H$7,0, IF($G$3:$G$7>($C3+1*($C3<$B3)), 0, IF(($C3+1*($C3<$B3))>$H$3:$H$7,$H$3:$H$7,($C3+1*($C3<$B3)))- IF($B3>$G$3:$G$7,$B3,$G$3:$G$7) ) )*$I$3:$I$7)*24alex201280 , you are welcome
alex201280 Please see the attached workbook for suggestions.
I do not think that your problem can be solved with just a few formulas because there are a lot of possible cases. It would be easier if you have some umbrella rules such as the total consecutive hours in a shift cannot exceed a certain number of hours, etc. Anyhow, with a bunch of helper columns (which you could hide), the good news is that all my testing cases return correct results. I may not have tested all possible scenarios so let me know of any case it does not work.
