Setting "Date Completed" column date based on status change

JesseE

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!

Mike Wilday

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! 

JesseE

Thanks Mike! We use Zapier so hopefully we can accomplish this.

Mike Wilday

You can now set a date using Workflow and the set a date Action:

https://help.smartsheet.com/articles/2481986-set-the-current-date-with-record-a-date-action

Jorge Villarreal

https://community.smartsheet.com/discussion/comment/273949#Comment_273949

Thanks for coming back to reply to this thread. Very helpful workflow. Thanks!

Stepheni Norton

=IF(Status@row = "Complete", Modified@row, "")

Then we lock the row once it is changes to Complete via an Automation workflow.