Forum Discussion
jvella13
Jun 21, 2024Copper Contributor
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...
djclements
Jun 21, 2024Silver Contributor
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...