Setting "Date Completed" column date based on status change
Hi! I'm trying to set up a formula for my Date Completed column that pre-populates the date in that field to the date where the Status on the same line = "Complete".
Right now I'm using this formula: =IF(OR(Status125 = "Complete"), TODAY(), "")
One problem is that it doesn't add the formula for newly added line items. Also - it literally changes the date to "today" and doesn't remember when the status flipped to "Complete".
Any help is appreciated!
Comments
-
The only way to complete what you are requesting is to use a third party app like Zapier to record the date of the change for you. I am not experienced in the tool but J. Craig Williams is and he created this post: http://ronin-global.com/2016/12/11/smartsheet-fixed-date-upon-completion/
Check it out of you seriously want to implement this functionality. We adjusted our processes because we don't have the budget to add Zapier to our tool belt. Instead of having people change a status, we are having people apply dates for the processes we are trying to track. In essence this replaces checkboxes, dropdowns, or the likes. It does require new rows, but if you're trying to secure a date anyway, why not just have them put the date in the cell.
Hope one of those two suggestions help!
-
Thanks Mike! We use Zapier so hopefully we can accomplish this.
-
You can now set a date using Workflow and the set a date Action:
-
Thanks for coming back to reply to this thread. Very helpful workflow. Thanks!
-
=IF(Status@row = "Complete", Modified@row, "")
Then we lock the row once it is changes to Complete via an Automation workflow.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 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!