Forum Discussion

jvella13's avatar
jvella13
Copper Contributor
Jun 21, 2024

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

Hi, I have a sheet that lists appointment dates on separate lines within a cell. I want a formula that will count the number of dates that appear in the cell that occurred before a specified date loc...
Formulas and Functions
djclements's avatar
djclements
Silver Contributor
Jun 21, 2024

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...

Resources