Count Cells with greater than Date, when Cells Contain Multiple Dates in Line Break w/ Custom Format

Copper Contributor

Hello everybody,

 

I have cells containing names in text format and dates in a custom format:  mm\/dd\/yy.  Some cells contain multiple dates on separate line breaks within the cell.  How do you count the number of records that are an "apple" and that also contain a date greater than October 1, 2018?

 

For example, in the attached case such a formula would return 2 since there are two records that contain "apple"and also contain a date greater than 10/01/18 on at least one line within the cell.  Spreadsheet is attached.  Thanks in advance for your help.

 

Annotation 2019-02-14 125049.jpg

 

 

1 Reply

Hi,

 

In column C you actually have mix of numbers (aka dates) and texts which only looks like dates. To check condition it's a bit complicated - check if the value is text or not; if text split on values separated by line feed, convert each such value to date, build sequence of all values and check if they are against criteria text in left column.

 

Perhaps it's easier to restructure your data to ensure one date per cell?