Home

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

%3CLINGO-SUB%20id%3D%22lingo-sub-339853%22%20slang%3D%22en-US%22%3ECount%20Cells%20with%20greater%20than%20Date%2C%20when%20Cells%20Contain%20Multiple%20Dates%20in%20Line%20Break%20w%2F%20Custom%20Format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-339853%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20everybody%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20cells%20containing%20names%20in%20text%20format%20and%20dates%20in%20a%20custom%20format%3A%26nbsp%3B%20mm%5C%2Fdd%5C%2Fyy.%26nbsp%3B%20Some%20cells%20contain%20multiple%20dates%20on%20separate%20line%20breaks%20within%20the%20cell.%26nbsp%3B%20How%20do%20you%20count%20the%20number%20of%20records%20that%20are%20an%20%22apple%22%20and%20that%20also%20contain%20a%20date%20greater%20than%20October%201%2C%202018%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%2C%20in%20the%20attached%20case%20such%20a%20formula%20would%20return%202%20since%20there%20are%20two%20records%20that%20contain%20%22apple%22and%20also%20contain%20a%20date%20greater%20than%2010%2F01%2F18%20on%20at%20least%20one%20line%20within%20the%20cell.%26nbsp%3B%20Spreadsheet%20is%20attached.%26nbsp%3B%20Thanks%20in%20advance%20for%20your%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F75298iA8C7CFFE61EAFF81%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Annotation%202019-02-14%20125049.jpg%22%20title%3D%22Annotation%202019-02-14%20125049.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-339853%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%20Desktop%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ENeed%20Help%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-339965%22%20slang%3D%22en-US%22%3ERe%3A%20Count%20Cells%20with%20greater%20than%20Date%2C%20when%20Cells%20Contain%20Multiple%20Dates%20in%20Line%20Break%20w%2F%20Custom%20Fo%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-339965%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20column%20C%20you%20actually%20have%20mix%20of%20numbers%20(aka%20dates)%20and%20texts%20which%20only%20looks%20like%20dates.%20To%20check%20condition%20it's%20a%20bit%20complicated%20-%20check%20if%20the%20value%20is%20text%20or%20not%3B%20if%20text%20split%20on%20values%20separated%20by%20line%20feed%2C%20convert%20each%20such%20value%20to%20date%2C%20build%20sequence%20of%20all%20values%20and%20check%20if%20they%20are%20against%20criteria%20text%20in%20left%20column.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPerhaps%20it's%20easier%20to%20restructure%20your%20data%20to%20ensure%20one%20date%20per%20cell%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
kana723
Occasional Visitor

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? 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies