Why isn't MAX returning the most recent DATE?

Hi All,

I'm trying to use MAX to show the most recent date in column that's "Auto-number/System - Modified (Date)", but frustratingly it's not always correct.

Anyone have any idea as to why? And more importantly, how to fix?

Best Answer

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓

    Hi @Andrew_Bull ,

    The Modified column includes a time stamp that causes issues. Try inserting a date helper column [Mod Date] with the column formula =DATEONLY([modified]@row). This removes the date stamp.

    Then run your MAX against the helper column. =MAX([Mod date]:[Mod date])

    Help?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓

    Hi @Andrew_Bull ,

    The Modified column includes a time stamp that causes issues. Try inserting a date helper column [Mod Date] with the column formula =DATEONLY([modified]@row). This removes the date stamp.

    Then run your MAX against the helper column. =MAX([Mod date]:[Mod date])

    Help?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Andrew_Bull
    Andrew_Bull ✭✭
    edited 01/17/21

    Thanks @Mark Cronk ,

    It seems to have, although every date has just defaulted to today given i created a new column and applied that formula to each row. I guess I'll find out over the next couple of days when rows start updating and I have a date range to work with.

    Appreciate your advice.

    AB.

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭

    Happy to help. Thank you for contributing to the Community.

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!