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

Copper Contributor

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/20196 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

@jvella13 

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)

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

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