Automatic date update in a cell when another cell's value changes (as calculated by a formula)

Emma Smyth

Hello

I would like to have a field set up that updates the date every time the 'status' changes in another column. Is this possible?

For example, when a new line item is added and the status column changes to 'on hold' I would like the date the status changed to show in another column. This date will stay the date since the status was put to 'on hold' and the date will only update when the status changes again.

Paul Newcome

When you go into the column properties to change the column type, or when you create a new column. you can select the system generated type and there will be an option for a Modified (date) which will output the date/time that something in the row was last changed.

Paul Newcome

Not necessarily. As long as there are two rows of the same hierarchy immediately above or below that contain the formula, it will autofill when the new row is added.

Paul Newcome

Have you looked into the system generated Modified (date) column type?

Emma Smyth

No, I also do not know what that is. Do you mind explaining?

Paul Newcome

When you go into the column properties to change the column type, or when you create a new column. you can select the system generated type and there will be an option for a Modified (date) which will output the date/time that something in the row was last changed.

Emma Smyth

Awesome! That should work perfectly.

I have a follow up question now. I would like to set a reminder for one month or bi-weekly from the modified date column. However when I choose the trigger "when a date is reached" I select the date field and the modified date field does not show up. How would I create a reminder based on the modified date?

Paul Newcome

Create a helper column that can then be hidden to keep the sheet looking clean. In every row of this helper column use

=DATEONLY(Modified@row)

Emma Smyth

I'm assuming this equation will have to be dragged down whenever a new row is added?

Paul Newcome

Not necessarily. As long as there are two rows of the same hierarchy immediately above or below that contain the formula, it will autofill when the new row is added.

Melissa Kosiewicz

We are looking to do this also, so that we can create a filter of all RAID items that have a status change from Open to Closed in the past week. We are already using the modified by column to show all changes, but are looking to have a column that will only show the date when the status column changes. Is this possible?

Paul Newcome

@Melissa Kosiewicz It is not possible directly within Smartsheet, but it is possible using 3rd party apps such as Zapier. I do not know a whole lot about that particular app, but I have seen it recommended quite a few times for this particular situation. @Andrée Starå knows much more about it than I do.

Andrée Starå

Thanks @Paul Newcome

Hi @Melissa Kosiewicz

There are two options. As Paul mentioned, Zapier or, if possible, we can keep it in Smartsheet with a solution I developed where you can store the date/value.

You'd use the copy-row automation and a VLOOKUP or combination of INDEX/MATCH to make it work.

We'd trigger the copy-row to another sheet and get the created date/other value and then use the VLOOKUP/INDEX/MATCH to get it back to the main sheet in another so-called helper column. As long as you have a unique id/values that we can use, it will work.

Also, this opens up more options with auto-numbering and more.

Make sense?

Would any of those options work/help?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Paul Newcome

@Andrée Starå I had thought about the copy row automation, but I must have gotten my posts mixed up. I had remembered reading somewhere (apparently somewhere else) about wanting to avoid using the extra sheet.

Andrée Starå

@Paul Newcome

Been there done that! Easy to get it mixed up.

sravya.gupta127326

@Andrée Starå - Is this still the best solution - I was looking for a solution for something very similar as above and would like to avoid creating a duplicate sheet or using a third party app.

Thanks!

Andrée Starå

Hi @sravya.gupta127326

I would say yes.

Here’s a post on the method I developed.

https://community.smartsheet.com/discussion/68589/lock-or-store-date-value-solution-without-using-zapier

Would that work?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

sravya.gupta127326

Thank you @Andrée Starå - I do have auto numbered row ID that I have used but couldn't quite get the index match working as there are still multiple rows with the match. So, I used max and collect function instead to get the last updated value.👍️