Return Yes/No Boolean or String if Date is the Latest for Each Project

Hello there,

I'm stuck on this and would love some help. I'm working on some automations in smartsheet and I need a column formula that returns the latest date of a project update that was entered via smartsheet form.

My rows are each project update that's been filled via form, and there are many different projects. I have a "Created Date" column that automatically populates every time someone fills out a project entry form.

The unique identifier is the project name. I want to be able to look at all the projects with the same name, compare their "Created Date", and return which row had the latest date.

Example:

Project Name 1 - Update1, 3/18/22 -> Return Yes

Project Name 1 - update2, 3/10/22 -> Return No

...

Project Name 2 - Update1, 3/18/22 -> Return Yes

Project Name 2 - Update2, 3/10/22 -> Return No

Project Name 2 - Update3, 3/13/22 -> Return No

...

Project Name n


So far I was able to get a column formula that compares all the projects with the same name and returns the latest "Created Date", which is the last time that project was updated via smartsheet form.

=MAX(COLLECT([Created Date]:[Created Date], [Official Project Name]:[Official Project Name], [Official Project Name]@row))


Please let me know if my question is not clear. Thank you!

Best Answer

  • Paul H
    Paul H ✭✭✭✭✭✭
    Answer ✓

    I typically add a checkbox column to find the last entry

    Then I use a formula like this to check it

    =IF([Created Date]@row = MAX(COLLECT([Created Date]:[Created Date], [Official Project Name]:[Official Project Name], [Official Project Name]@row)), 1)

    Now I can look for the checkmark to find to the latest info.

Answers

  • Paul H
    Paul H ✭✭✭✭✭✭
    Answer ✓

    I typically add a checkbox column to find the last entry

    Then I use a formula like this to check it

    =IF([Created Date]@row = MAX(COLLECT([Created Date]:[Created Date], [Official Project Name]:[Official Project Name], [Official Project Name]@row)), 1)

    Now I can look for the checkmark to find to the latest info.

  • Thank you Paul!! That worked! I changed it slightly to have a yes/no return and it also works.

    =IF([Created Date]@row = MAX(COLLECT([Created Date]:[Created Date], [Official Project Name]:[Official Project Name], [Official Project Name]@row)), "Yes", "No")

    Appreciate your help :)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!