Problem

I have created quite a convoluted formula in Google Sheets that Sheets doesn't seem powerful enough to handle for more than ~80 rows of data, so I wanted to try moving it over to Excel. Seems like Excel doesn't do "arrays in arrays," which I'm doing by putting a MAP inside of a MAP, so I'm wondering if any Excel wizards can take a look at this formula and make it work in Excel.

Background

I work at a company where a case owner owns a case from a specific start date/time to an end date/time. That case owner can own multiple cases at once. I'm trying to figure out the "labor cost" per case, which is simple to do if they were to own one case at a time. For example, if they're paid $20/hour and work a case from 1/1/25 at 2PM to 1/8/25 at 9AM, they'd have worked 40 hours, assuming an 8AM to 5PM workday and not including weekends, meaning the labor cost for that case is 40*$20=$800.

Things get trickier when, for example, they take up a 2nd case while working that first one. For a simple example, say they started working a 2nd case on 1/7/25 at 4PM. That means there were 2 hours in which they were working two cases. So the labor cost equation for the first case would now be (38*$20)+(2*$20)/2=$780. In other words, we're now multiplying 39 by $20, which is (38 hours/1 case) + (2 hours/2 cases). The equation I created finds this multiplier.

Sheets Solution

Below is my equation in the "Multiplier" column for the example table below:

=LET(
createDate,A2:A8,
retainDate,ARRAYFORMULA(B2:B8-1/24),
names,C2:C8,
morningTimes,SEQUENCE(7,1,ROUND(TIME(8,0,0),3),0),
eveningTimes,SEQUENCE(7,1,ROUND(TIME(17,0,0),3),0),
sundays,SEQUENCE(7,1,1,0),
saturdays,SEQUENCE(7,1,7,0),
MAP(createDate,retainDate,names,LAMBDA(create,retain,name,
SUM(MAP(SEQUENCE((retain-create+1/24)*24,1,create,1/24),LAMBDA(t,IFERROR(1/SUMPRODUCT(names=name,ROUND(t,6)>=ARRAYFORMULA(ROUND(createDate,6)),ROUND(t,6)<=ARRAYFORMULA(ROUND(retainDate,6)),morningTimes<=ROUND(MOD(t,1),3),eveningTimes>ROUND(MOD(t,1),3),saturdays<>WEEKDAY(t),sundays<>WEEKDAY(t)),0)))))))

Here is a direct translation of your formula into Excel for interest:

=LET( createDate,A2:A8, retainDate,(B2:B8), names,C2:C8, morningTimes,SEQUENCE(7,1,ROUND(TIME(8,0,0),3),0), eveningTimes,SEQUENCE(7,1,ROUND(TIME(17,0,0),3),0), sundays,SEQUENCE(7,1,1,0), saturdays,SEQUENCE(7,1,7,0), days,SEQUENCE(7), REDUCE("Multiplier",days,LAMBDA(a,i, VSTACK(a,SUM(MAP(SEQUENCE((INDEX(retainDate,i)-INDEX(createDate,i))*24,1,INDEX(createDate,i),1/24),LAMBDA(t,IFERROR(1/SUMPRODUCT((names=INDEX(names,i))*(ROUND(t,6)>=(ROUND(createDate,6)))*(ROUND(t,6)<=(ROUND(retainDate-1/24,6)))*(morningTimes<=ROUND(MOD(t,1),3))*(eveningTimes>ROUND(MOD(t,1),3))*(saturdays<>WEEKDAY(t))*(sundays<>WEEKDAY(t))),0))))))))

Changes:

(1) Change outer Map into Reduce

(2) Reduce only has one 'current' parameter, so need to use this as a subscript referring to createDate etc. using Index

(3) Defined 'days' as a sequence from 1 to 7 for use with Reduce.

(4) Sumproduct does not work with logical values - have to replace commas with * throughout.

(5) Moved 1/24 out of retainDate definition - I think there is a rounding issue here that stops it working properly otherwise - haven't been able to pin it down.

(6) Removed all ArrayFormula references - Excel 365 assumes array formulas by default.