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

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
Is it possible for Edge to play HEVC (H265) MKV files ?
HotCakeX in Discussions on
2 Replies
Date format in Stream
Dave Pyett in Microsoft Stream Forum on
2 Replies
Counting Days
Tim Hunter in SQL Server on
2 Replies
Date filtering of Bing Searches
PhilipE3 in Discussions on
2 Replies