SOLVED

New Contributor

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.

4 Replies
Highlighted

Re: Formula calculating earnings during one shift when three different rates of pay apply

@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.

Highlighted
Best Response confirmed by alex201280 (New Contributor)
Solution

Re: Formula calculating earnings during one shift when three different rates of pay apply

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``````
Highlighted

Re: Formula calculating earnings during one shift when three different rates of pay apply

Brilliant. Thank you! Much appreciated indeed.

Highlighted

Re: Formula calculating earnings during one shift when three different rates of pay apply

@alex201280 , you are welcome