Forum Discussion
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
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...
- 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" 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
