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

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

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.

Best Answers

Answers

  • Paul NewcomePaul Newcome ✭✭✭✭✭

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

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

  • 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 NewcomePaul 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([email protected])

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

  • edited 05/20/20

    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 NewcomePaul 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åAndrée Starå ✭✭✭✭✭
    edited 05/22/20

    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!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

  • Paul NewcomePaul 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.

Sign In or Register to comment.