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

Options

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 ✓
    Options

    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 ✓
    Options

    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.

  • Raghda_123
    Options

    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!