Excel formula - Show value in dedicated cell based on various values in multiple cells.

JTrehus 

Since this thread raised its head again, I attempted to match HansVogelaar 's solution but restricting myself to a single dynamic formula and absolute references only.  I realise this is of no value to the OP and only a few readers will have the necessary version of Excel.

I started by using SCAN but came to the conclusion that I was going to need MAKEARRAY to display the resulting 'array of arrays'.  I therefore chose to switch to MAKEARRAY from the outset.  The result was a solution process that very much reflects the legacy spreadsheet strategy of developing a formula for a single cell and copying across and down the relevant number of cells.  The difference is that, rather than relying upon implicit intersection, I used a representative row and column index within the result array to determine the values to use within each calculation.  

I seem to remember that SergeiBaklan regards MAKEARRAY as somewhat ugly, but I don't think it is that bad used when used to feed values into a Named Lambda function.  Something else that caused me grief with a single formula solution is that the early morning hours need to be combined with the evening hours when calculating hours worked at double time.  The mechanics of this calculation were to use MMULT but I chose to hide the formula step by defining a further Lambda function

CombineNightHrsλ.

= CombineNightHrsλ(
     MAKEARRAY(ROWS(Date),5,OvertimeHoursλ)
  )

With a bit of luck, by the time these functions come out of beta, I might know what I am doing!

At least, from Hans's sheet, I now know what the first and second days of Christmas are in Dutch; I never made it past 'dank je wel' before. 

JTrehus 

JTrehus 

It may be possible but it won't be trivial.  Using 365 insider beta my formula was

= MAKEARRAY(2,4,
      LAMBDA(d,shift,
         LET(
            s,          INDEX(Start,d),
            e,          INDEX(End,d),
            wd,         IF(WEEKDAY(INDEX(Date,d))=1,1,2),
            shiftS,     INDEX(bandS,wd,shift),
            shiftE,     INDEX(bandE,wd,shift),
            laterStart, IF(s<shiftS, shiftS, s),
            earlierEnd, IF(e<shiftE,e,shiftE),
            hrs, earlierEnd - laterStart,
            IF(hrs>0, hrs, 0)
         )
      )
   )

I have used a Lambda helper function to produce all the results as a single formula but, using traditional Excel, it should be possible to pick the references out as relative references for each cell.  The Let would need to be replaced by a nested formula but that is not my scene.

JTrehus 

In E3:

=IF(A3="Sunday",0,MIN(D3-C3,TIME(8,0,0)))

In F3:

=IF(A3="Sunday",0,D3-C3-E3)

In G3:

=IF(A3="Sunday",D3-C3,0)

Fill down.