Extract MAX date from comma separated string in a cell
Best Answer
-
If the Scenario Date is concatenated from a different sheet, could you instead reference this other sheet and possibly use a MAX/COLLECT type of formula to pull the max date directly from the source?
Exactly what formula are you using to pull the string? I imagine probably a JOIN/COLLECT? If so, replace "JOIN" with "MAX" and remove the delimiter portion.
=JOIN(COLLECT(..............................), ", ")
=MAX(COLLECT(..............................))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
What is the highest number of dates possible within a single string? You will end up needing enough helper columns to accommodate parsing them out then use the MAX function across these helper columns.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Hi Paul,
The dates in single string could be from 1 to ~30. It varies for each row so not sure how can we have helper columns to accommodate it. Any help here?
For time being I have used below approach.
- Scenario Date is a concatenated Text and referenced from mapping sheet. I have sorted this date field in descending order so that I will get Latest/Max date first always.
- Then I have used LEFT and FIND function to extract first date from the string. =LEFT([Scenario Date]@row, FIND(",", [Scenario Date]@row) - 1) The only catch here is I need to sort my mapping sheet manually whenever I'm adding/updating dates.
Please let me know if there is any other simple way to achieve it. Thanks.
-
If the Scenario Date is concatenated from a different sheet, could you instead reference this other sheet and possibly use a MAX/COLLECT type of formula to pull the max date directly from the source?
Exactly what formula are you using to pull the string? I imagine probably a JOIN/COLLECT? If so, replace "JOIN" with "MAX" and remove the delimiter portion.
=JOIN(COLLECT(..............................), ", ")
=MAX(COLLECT(..............................))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Thanks much Paul,
Your solution worked..
Regards,
Nazeer
-
Happy to help. 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!