Setting "Date Completed" column date based on status change

JesseE · 01/18/19

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 · 01/18/19

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 · 01/18/19

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

Mike Wilday · 01/06/21

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 · 08/27/21

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

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

Stepheni Norton · 11/03/21

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

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

Setting "Date Completed" column date based on status change

Comments

Help Article Resources

Categories