Update Date when Health Changes

@emipathy

Hi All,

I've been tasked with finding a way to automate a column in a health-tracker for our PMO. Here is the ask:

"When [Health]@row changes to Any Value (red/yellow/green/grey), update the [Date of Change]@row with the date the change occured"

Simply, we need a way to automatically track what day the change in health happens. Not many people are in the sheet, as it has multiple feeds, and the 'Date of Change' column often gets neglected.

I'm unfamiliar with returning dates in Smartsheet.

Does anyone have any tips for how to start a formula like this? Or anything similar?

Thanks!

Paul Newcome

Something like this would require either manual entry of the date or a third party service such as Zapier.

Andrée Starå

Hi,

The third-party service, Zapier, is an excellent option for this scenario. Is that an option for you?

I hope that helps!

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

Andrew Stewart

Hi @empathy,

If you only care about a specific row, you can right click on the Health cell and select View cell history. This gives a GREAT audit trail of changes, including the value, the person and the date and time of the change. You could then manually fill in the [Date of Change] field if somebody has forgotten.

You could also to use conditional formatting to make the Date of Change go red if it is blank and the status is not blank, to reduce the likelihood of people forgetting to update the field. But this will not help if the health is initially Green and then is set to Red, for instance, since the field has already been updated.

You could also set up a workflow to send you (or a shared mailbox) a notification each time the Health field changes in value, which might be useful if a manual escalation/approval process is required.

A workflow can lock the row that triggered it, so I assume it would not be complex to add an action to update a field in the row based on a formula referencing values in other fields of that row.

You should lodge an enhancement request at https://app.smartsheet.com/b/form?EQBCT=739aa75f30ca43a8a22eb53e4da7d409&_ga=2.147299634.46783086.1578533604-1348825732.1578533604

Having said that, I have found I get no feedback on the status of the request, nor have any visibility of requests from others. I am pretty sure I have submitted this one already, possibly more than once!

I have tried using Zapier to do this, but found that the Zap has to run whenever ANY cell has changed in a row, and do nothing if it is not the cell you care about. The problem is that when a row is inserted, the rowid for all subsequent rows changes, and the Zap fires for each. If there are many, the Zap gets throttled. Not an issue if you always insert rows at the end, but something to be aware of.