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

[alex201280]

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.

[#2]

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

[#3 · Accepted solution]

@alex201280 

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]

@Sergei Baklan 

Brilliant. Thank you! Much appreciated indeed.

[#5]

@alex201280 , you are welcome