Forum Discussion

djclements's avatar
djclements
Silver Contributor
Jun 21, 2024

Re: compare several dates in a single cell on multiple lines to a single date in another cell

jvella13 With Excel for MS365, perhaps something along these lines:

 

=LET(
    array, TEXTSPLIT(SUBSTITUTE(A2, CHAR(10), " "), " "),
    dates, DATEVALUE(FILTER(array, ISNUMBER(SEARCH("/??/", array)), "01/01/1900")),
    SUM( (dates > EDATE(B2, -6)) * (dates <= B2) )
)

 

Sample Results

 

EDIT: Sorry, I misread your previous comment... since each line always begins with a date, the following simplified formula would also work:

 

=LET(
    dates, DATEVALUE(TEXTBEFORE(TEXTSPLIT(A2, CHAR(10)), " ")),
    SUM( (dates > EDATE(B2, -6)) * (dates <= B2) )
)

 

See attached...

No RepliesBe the first to reply