Updating Date Column if Another Column has been Changed

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?

Answers

  • Brian Campbell
    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



    Michael

  • Brian Campbell
    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.

    Michael

  • 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å
    Andrée Starå ✭✭✭✭✭✭

    Hi @Priscilla Awad

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

    More info: 


    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!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!