Creating a Date completed Automation
I am attempting to create a formula that returns the date when my "Completed" column = 100%. Smartsheet keeps saying my syntax is incorrect. From what I can tell I need to use the TODAY function paired with an addition column that acts as a check to prevent it from perpetually updating the cell. In English the formula for Date Completed column would be: If completed = 100% & check column is not checked return Today's Date. And the checked column would be: If Date Completed, is not blank, check column.
The two formulas I have as of right now are
Completed Date:
=IF(AND([Completed]@row = 100, [Check]@row = 0), TODAY(), )
and
Check:
=IF(NOT(ISBLANK([Completed Date]@row)), 1, 0)
Edit: I realize now that this is a circular reference. I think I need a third column to do some sort of storage check but IDK what exactly.
Best Answer
-
Is there a reason you are not using the "Record a Date" automation instead?
Answers
-
Is there a reason you are not using the "Record a Date" automation instead?
-
Every time I try the automation, when it updates, it is autofilling the entire column with the same date rather than just the rows that have completed at 100%. The automation as I understand it should be something along the lines of If column "completed" =100% then record date in "Completed Date" column. I then ran the automation to the afore mentioned result.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.4K Get Help
- 396 Global Discussions
- 213 Industry Talk
- 448 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 133 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 293 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!