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

Copper 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 located within another cell.

example:

 appointments after 1/1/2019 6 months # appointments on or before 6 months 07/25/2022 Follow up Appointment - By Phone - 1 of 2 - 30 MIN 11/10/2022 Follow up Appointment - By Phone - 2 of 2 - 30 MIN 04/03/2023 Med Check - Video - 30 MIN 05/02/2023 Med Check - Video - 30 MIN 05/30/2023 Med Check - Video - 30 MIN 11/19/2022 (I want a formula that will return 2 here since 2 dates in column A are before the date in column B)

ETA:

would it help if i were able to reduce the cell data to only the dates without line breaks?

go from this:
"06/24/2022 Follow up Appointment - Video - 1 of 2 - 30 MIN with Joe Brown, M.D.
08/11/2022 Follow up Appointment - Video - 2 of 2 - 30 MIN with Joe Brown, M.D.
09/02/2022 Med Check - Video - 30 MIN with Joe Brown, M.D.
11/02/2022 Med Check - Video - 30 MIN with Joe Brown, M.D."

to this:
"06/24/2022 08/11/2022 09/02/2022 11/02/2022"

4 Replies

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

In C2:

=SUM(--(DATEVALUE(LEFT(A2:A6, 10))<B2))

If you do not have Microsoft 365 or Office 2021, you may have to confirm the formula by pressing Ctrl+Shift+Enter (Windows) or Command+Shift+Return (Mac)

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

dates are all in cell A2 and not spread out A2:A6. each line within the cell does begin with the date. there are cells with multiple lines from 2 lines to 32 lines

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

would it help if i were able to reduce the cell data to only the dates without line breaks?

go from this:
"06/24/2022 Follow up Appointment - Video - 1 of 2 - 30 MIN with Joe Brown, M.D.
08/11/2022 Follow up Appointment - Video - 2 of 2 - 30 MIN with Joe Brown, M.D.
09/02/2022 Med Check - Video - 30 MIN with Joe Brown, M.D.
11/02/2022 Med Check - Video - 30 MIN with Joe Brown, M.D."

to this:
"06/24/2022 08/11/2022 09/02/2022 11/02/2022"

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