I have a set of employees on a project and I have the following formula that calculates the number of employees active each month:

=MMULT(SEQUENCE(1,ROWS($A$20#),1,0),($L$1#>=OFFSET($A$20#,0,3,,1))*($L$1#<=OFFSET($A$20#,0,4,,1)))

Where $L$1# is defined by =EDATE(01/01/2020, SEQUENCE(1, 6, 0)) and $A$20# refernces the first entry in a dataset of employees as below:

This formula above will only cope with those employees on a project from the start of the month. I'd like it to increment the number of employees even if they are on it for a single day in the start month so giving the following result:

I tried modifying the formula to check if the Start Date is between the month start and month end defined by $L$1# but unfortunately this doesn't like the use of $L$1# in the EOMONTH function:

=MMULT(SEQUENCE(1,ROWS($A$20#),1,0),(AND((OFFSET($A$20#,0,3,,1)>=$L$1#),(OFFSET($A$20#,0,3,,1)<=EOMONTH($L$1#,0))))*($L$1#<=OFFSET($A$20#,0,4,,1)))

How would I go about adjusting the above to work with $L$1#?

CodePudding user response：

You can try the following in cell G1:

=LET(set, A2:E7, starts, INDEX(set,,4), ends, INDEX(set,,5), SOMs, G1:L1,
 BYCOL(SOMs, LAMBDA(SOM, LET(EOM, EOMONTH(SOM,0),
  overlaps, FILTER(starts, (starts <= EOM) * (ends >= SOM), 0),
  ROWS(overlaps)
 )))
)

There are multiple ways to achieve the same, for example using SUMPRODUCT or just SUM:

=LET(set, A2:E7, starts, INDEX(set,,4), ends, INDEX(set,,5), SOMs, G1:L1,
 BYCOL(SOMs, LAMBDA(SOM, LET(EOM, EOMONTH(SOM,0),
  SUMPRODUCT((starts <= EOM) * (ends >= SOM))
 )))
)

The main idea is to check for overlap condition of two intervals A, B:

AND(startA <= endB, endA >= startB)

Note: None of the above approaches use OFFSET a