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

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.