Jun 19 2020 04:38 AM
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.
Jun 19 2020 10:18 PM
@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.
Jun 20 2020 08:33 AM
SolutionFirst 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
Jun 20 2020 05:45 PM
Jun 21 2020 04:42 AM
@alex201280 , you are welcome
Jun 20 2020 08:33 AM
SolutionFirst 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