Updating Date Column if Another Column has been Changed

Priscilla Awad

I am trying to create a formula to change a Date column to update to the current date when another column has been changed. I have used the TODAY function within IF but this updates the current date every time the sheet is opened and saved. I only want the Date cell to change when a specific other cell is changed. Suggestions?

Brian Campbell

Good Morning Priscilla,

There is actually a modified date auto column that will do this by default. just choose the options below on the sheet. Whenever anything is changed on that row it will update

Brian Campbell

Just reread this, do you say you need this only if a specific column is changed? If so then there is an odd workaround that might work.

Priscilla Awad

Hi Michael,

Yes, I only need this if a specific column is changed. Currently I am using a nested IF statement but did not have a false statement. This is what support from Smartsheets has told me:

Putting a false value in the IF statement is optional and does not affect the result of your IF function. Smartsheet reads the IF statements in the formula from left to right, displaying a value based on the first expression to evaluate to true. When return_if_false argument was omitted, a blank value is returned by default.

 

If you want to put a false value in your nested IF statement, it is highly suggested to put it inside the last IF function of your nested IF formula same as the below sample:

 

=IF(STATUS1 = "Approved", TODAY(), IF(STATUS1 = "At Compliance Review", TODAY(), IF(STATUS1 = "NLD Review", TODAY(), return_if_false)))

 

Additionally, locking row or column allows you to protect key data from being changed or deleted by others who are shared with your sheet however does not prevent a formula-driven cell to update.

 

TODAY Function will not update to the current day only in these 2 circumstances:

 

·        A report looking at the function in the sheet is opened.

·        A dashboard showing information from the sheet is opened.

Andrée Starå

Hi @Priscilla Awad

Please have a look at my post below with a method I developed.

More info: 

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 week!

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!