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(..............................))
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.
-
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(..............................))
-
Thanks much Paul,
Your solution worked..
Regards,
Nazeer
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 140 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!