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
-
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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!