Forum Discussion

alex201280's avatar
alex201280
Copper Contributor
Jun 19, 2020
Solved

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:...
excel
  • SergeiBaklan's avatar
    SergeiBaklan
    Jun 20, 2020

    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
hynguyen's avatar
hynguyen
Iron Contributor
Jun 20, 2020

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.

Resources