Smartsheet: Fixed date upon completion

One of the FAQ’s in the Smartsheet Community is a asking for a way to have a “Completion Date” cell be updated automatically when a user sets a status to ‘completed’, a percent complete to 100%, checking a “Done” checkbox, or dozens of other ways a task can be marked as finished.

For example, a user will check that their task is completed. It would be nice if the Completion Date cell to the left would fill in automatically.

Smartsheet does not have a ‘toggle’ feature – that is a formula that will set a date to “today”, will set it to “today” again tomorrow and the date will be updated (improperly in all likelihood) in the future.

I’ve had a work-around to this for a while, but it required that no user updated anything else in the row after marking the row completed. In some use cases, this works. For many, it won’t. I won’t bother showing that formula here.

Using Zapier (www.zapier.com), the solution is quite easy, once you work out the kinks.

This requires a Basic account or higher, because it uses a multi-step Zap (what Zapier calls an automated process). Typical Zaps are trigger-action pairs. This one throws another filter action to prevent future updates to updating the row again.

The Zap’s trigger will be an Updated Row event. That’s one of the common triggers for Smartsheet in Zapier.

A Filter is next, to only pass on the rows that have a “Done” check box and an empty “Completion Date”.

 

An Action to reformat Smartsheet’s Modified Date/Time to only the date gets passed back to the updated row and the Completion Date is entered.

Step 3 is an Action trigger using Zapier’s own Formatter app to reformat the output of Step 1 to a more friendly looking Date format. Smartsheet would recognize the standard format, but I’ve seen instances where it was not predictable. Rather than argue with it, I just force it to be the Date format I want.

Step 4 is putting the data back into Smartsheet now that it has been filtered and reformatted to my liking.

And that’s all there is to it.

Update: 2017-02-20

The sheet is located here.

You can add an entry to my testing sheet using this WebForm. The WebForm will add a new row to my sheet with the [Done] checkbox checked. Depending on timing, the Zap will run shortly thereafter (they run every 15 minutes on the Basic Plan) and automatically update the [Task Completed Date] column with today’s date.

To test that the [Task Completed Date] does not update when the row is later changed, the [Task Name] can be updated later (at least a day after the initial entry).

If anyone would like help setting up this Zap or support in integrating other tools using Zapier, I can be contacted at craig.williams@ronin-global.com to receive a price list and our availability.

Update: 2017-05-17

Provided more details on steps 3 and 4.