Extract MAX date from comma separated string in a cell

Nazeer Sayyed
Nazeer Sayyed ✭✭
edited 03/26/21 in Formulas and Functions

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.


Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Answers

  • Paul Newcome
    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.

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • 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
    Paul Newcome ✭✭✭✭✭✭
    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Thanks much Paul,

    Your solution worked..


    Regards,

    Nazeer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!