Sign in to submit new ideas and vote
Get Started

Create a date column that can tell me the date that cells in another column last changed

Options

I need to be able to create a date column that can tell me the date that cells in another column last changed. Could be a column type or a formula

I don't have any way of ageing a data set at scale. I can look at individual cell history, but I need to date all cells in a column and sort or filter by age.

1
1 votes

Idea Submitted · Last Updated

Comments

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @Steven Sawyer

    What I would do in this instance is set up a regular Date column then use a Record a Date workflow to record when a change is made in that other column. Here's more information https://help.smartsheet.com/articles/2481986-set-the-current-date-with-record-a-date-action

    Let me know if this solves for your idea!

    Cheers,

    Genevieve

  • Steven Sawyer
    Steven Sawyer Overachievers
    Options

    I am testing this solution now. There seems to be quite a few restrictions:

    To know when working with change cell value

    • The sheet owner’s personal account settings define the time zone of the date set.
    • If someone deletes or changes a Record a date workflow’s target date field to another column type, the workflow gets disabled, and sheet admins receive an email notification.
    • In cell history and activity log, you see the change attributed to Smartsheet Automation
    • Record a date is a terminal action, and it doesn’t accept any other conditions or actions in the workflow.
    • Smartsheet doesn’t currently support relative dates (5 days from today, 3 days ago).
    • Record a date can’t set start and end dates that are automatically calculated by predecessor or parent roll-up dependencies.
    • Each time a Record a date workflow triggers, it overwrites any previously recorded date value.
    • To avoid creating endless loops, Smartsheet doesn't initiate the Record a date action when the trigger cell includes cross-sheet formulas or are linked to other cells. To work around this, use time-based automations or recurrence workflows.
    • Update cell actions need time to update the sheet, especially if there are multiple workflows. 

    I may have an issue with "Smartsheet doesn't initiate the Record a date action when the trigger cell includes cross-sheet formulas or are linked to other cells."

    Also, it wont retro record on the 2000 rows I already have in the sheet… just on rows that change from today forward. So I would still want a formula that looks at each cell's history and records the last date a change occurred.

    Ill turn it on and let it run a couple days to see what happens.

    Thanks for the info.