Extract MAX date from comma separated string in a cell

Nazeer Sayyed

Hi There,

I have a column [Scenario Date] with concatenated dates separated by comma. I want to extract MAX data from the string and show it in a [Scenario (Max) Date] column.

I know we can use MAX function if we have only one date in a cell and Date column. But need some help with below.

Paul Newcome

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(..............................))

Paul Newcome

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.

Nazeer Sayyed

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.

1. 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.
2. 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.

Paul Newcome

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(..............................))

Nazeer Sayyed

Thanks much Paul,

Your solution worked..

Regards,

Nazeer

Paul Newcome

Happy to help. 👍️