I have the following employee data in a table called StaffDetailsTbl on a sheet called Staff Details:

I have another sheet with the following monthly sequence:

In B4 I have the following code to calculate the numbers of active monthly staff:

=LET(set, StaffDetailsTbl, starts, INDEX(set,,5), ends, INDEX(set,,6), SOMs, $B$1#,
 BYCOL(SOMs, LAMBDA(SOM, LET(EOM, EOMONTH(SOM,0),
  SUMPRODUCT((starts <= EOM) * (ends >= SOM))
 )))
)

However, as seen in the image above, it's giving incorrect numbers. This is because it doesn't account for the Employment End Date being empty, so only counts Dave. Is there a way I can modify this formula to include the employee in the count after their Employment Start Date if there's no Employment End Date?

The data used for the StaffDetailsTbl is:

CodePudding user response：

I assume when the employment end date is left blank, the headcount shall be continued for that person indefinitely.

Without trying to make it elegant, try the following adjustment:

=LET(set, StaffDetailsTbl, starts, INDEX(set,,5), ends, INDEX(set,,6), SOMs, $B$1#,
 BYCOL(SOMs, LAMBDA(SOM, LET(EOM, EOMONTH(SOM,0),
  SUMPRODUCT((starts <= EOM) * ((ends >= SOM)   ISBLANK(ends)))
 )))
)

So instead of only multiplying with (ends >= SOM), you multiply with ((ends >= SOM) ISBLANK(ends)) which is either 1 or 0 and shouldn't be able to be 2, because when (ends >= SOM) is TRUE, it cannot be blank so the second statement ISBLANK(ends) will then be FALSE, and vice versa.

Note: in case the end dates are actually not truly blank but just an empty string (because of a formula), then instead of ISBLANK(ends) use LEN(ends)=0.