Discussion Re: Formula calculating earnings during one shift when three different rates of pay apply in Excel
https://techcommunity.microsoft.com/t5/excel/formula-calculating-earnings-during-one-shift-when-three/m-p/1479106#M66353
<P><LI-USER uid="521"></LI-USER> </P><P> </P><P>Brilliant. Thank you! Much appreciated indeed.</P>Sun, 21 Jun 2020 00:45:53 GMTalex2012802020-06-21T00:45:53ZFormula calculating earnings during one shift when three different rates of pay apply
https://techcommunity.microsoft.com/t5/excel/formula-calculating-earnings-during-one-shift-when-three/m-p/1476822#M66195
<TABLE width="333"><TBODY><TR><TD width="77"><P>Start</P></TD><TD width="77">End</TD><TD width="77">Hours</TD><TD width="102">Earnings</TD></TR><TR><TD>10:00:00</TD><TD>20:00:00</TD><TD>10:00:00</TD><TD> </TD></TR><TR><TD>08:00:00</TD><TD>18:00:00</TD><TD>10:00:00</TD><TD> </TD></TR><TR><TD>15:00:00</TD><TD>00:00:00</TD><TD>09:00:00</TD><TD> </TD></TR><TR><TD>10:00:00</TD><TD>20:00:00</TD><TD>10:00:00</TD><TD> </TD></TR><TR><TD>10:00:00</TD><TD>20:00:00</TD><TD>10:00:00</TD><TD> </TD></TR><TR><TD>14:00:00</TD><TD>22:00:00</TD><TD>08:00:00</TD><TD> </TD></TR><TR><TD>14:00:00</TD><TD>22:00:00</TD><TD>08:00:00</TD><TD> </TD></TR><TR><TD>23:00:00</TD><TD>08:30:00</TD><TD>09:30:00</TD><TD> </TD></TR><TR><TD>10:00:00</TD><TD>20:00:00</TD><TD>10:00:00</TD><TD> </TD></TR><TR><TD>10:00:00</TD><TD>20:00:00</TD><TD>10:00:00</TD><TD> </TD></TR><TR><TD>22:00:00</TD><TD>08:30:00</TD><TD>10:30:00</TD><TD> </TD></TR><TR><TD>22:00:00</TD><TD>08:30:00</TD><TD>10:30:00</TD><TD> </TD></TR></TBODY></TABLE><P> </P><P>The rates of pay are:</P><TABLE width="264"><TBODY><TR><TD width="88">Rate of Pay</TD><TD width="88">Start</TD><TD width="88">End</TD></TR><TR><TD>£10</TD><TD>07:00:00</TD><TD>19:00:00</TD></TR><TR><TD>£15</TD><TD>19:00:00</TD><TD>22:00:00</TD></TR><TR><TD>£20</TD><TD>22:00:00</TD><TD>07:00:00</TD></TR></TBODY></TABLE><P> </P><P>So for instance, if someone worked 15:00:00 - 00:00:00 they would be paid £125: </P><P> - 15:00:00 - 19:00:00 @£10/hr = £40</P><P> - 19:00:00 - 22:00:00 @£15/hr = £45</P><P> - 22:00:00 - 00:00:00 @£20/hr = £40</P><P> </P><P>I've managed to come up with a formula that will calculate number of hours worked: </P><P>=IF(AC19>AB19,AC19-AB19,1-AB19+AC19)</P><P> </P><P>I need a formula that will tell me how much is earned during each shift.</P><P> </P><P>Many thanks.</P>Fri, 19 Jun 2020 11:38:41 GMThttps://techcommunity.microsoft.com/t5/excel/formula-calculating-earnings-during-one-shift-when-three/m-p/1476822#M66195alex2012802020-06-19T11:38:41ZRe: Formula calculating earnings during one shift when three different rates of pay apply
https://techcommunity.microsoft.com/t5/excel/formula-calculating-earnings-during-one-shift-when-three/m-p/1478266#M66286
<P><LI-USER uid="704604"></LI-USER> Please see the attached workbook for suggestions.</P><P>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.</P>Sat, 20 Jun 2020 05:18:11 GMThttps://techcommunity.microsoft.com/t5/excel/formula-calculating-earnings-during-one-shift-when-three/m-p/1478266#M66286hynguyen2020-06-20T05:18:11ZRe: Formula calculating earnings during one shift when three different rates of pay apply
https://techcommunity.microsoft.com/t5/excel/formula-calculating-earnings-during-one-shift-when-three/m-p/1478692#M66311
<P><LI-USER uid="704604"></LI-USER> </P>
<P>First I'd define rates for this and next days as</P>
<P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="image.png" style="width: 580px;"><img src="https://techcommunity.microsoft.com/t5/image/serverpage/image-id/199879i33031E5AA7288FD1/image-size/large?v=1.0&px=999" role="button" title="image.png" alt="image.png" /></span></P>
<P>Next day is in bold here.</P>
<P>With that formula could be</P>
<LI-CODE lang="excel">=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</LI-CODE>Sat, 20 Jun 2020 15:33:32 GMThttps://techcommunity.microsoft.com/t5/excel/formula-calculating-earnings-during-one-shift-when-three/m-p/1478692#M66311Sergei Baklan2020-06-20T15:33:32ZRe: Formula calculating earnings during one shift when three different rates of pay apply
https://techcommunity.microsoft.com/t5/excel/formula-calculating-earnings-during-one-shift-when-three/m-p/1479106#M66353
<P><LI-USER uid="521"></LI-USER> </P><P> </P><P>Brilliant. Thank you! Much appreciated indeed.</P>Sun, 21 Jun 2020 00:45:53 GMThttps://techcommunity.microsoft.com/t5/excel/formula-calculating-earnings-during-one-shift-when-three/m-p/1479106#M66353alex2012802020-06-21T00:45:53ZRe: Formula calculating earnings during one shift when three different rates of pay apply
https://techcommunity.microsoft.com/t5/excel/formula-calculating-earnings-during-one-shift-when-three/m-p/1479370#M66369
<P><LI-USER uid="704604"></LI-USER> , you are welcome</P>Sun, 21 Jun 2020 11:42:08 GMThttps://techcommunity.microsoft.com/t5/excel/formula-calculating-earnings-during-one-shift-when-three/m-p/1479370#M66369Sergei Baklan2020-06-21T11:42:08Z