How would you most easily track that a date has changed to a new date that is 'x' different (+,-)?

Annaleah Morrow

We are using data shuttle to bring 'start date' in from another system (upon attachment for now; eventually, most recent when it sees it). If the start date changes we have users who want to know if it moved up 10 days, back 3, etc. I can create a hidden column to hold information and calculate - but I can't figure out how to get a report within history or grab the old value before it's changed.

So, today the 'start date' column might say 5/1/24, but tomorrow a user or the automation may change it to 5/15/24. Ideally I would like to be able to report somewhere that the date changed 15 days.

Ideas?

Paul Newcome

You would first need a column that has a unique identifier on every row. Then you would need to set up a copy row automation that copies the rows from this sheet to a second sheet whenever a change is made. You are going to want to copy every row to this second sheet to start a "baseline". All rows will (after the setup is complete) show as zero days change for now but then will start recording the change once changes start happening after setup.

The final step is to create the column formula that will output the number of days changed on your first sheet.

=IFERROR(INDEX(COLLECT({Second Sheet Start Date Column}, {Second Sheet Unique ID Column}, @cell = [Unique ID]@row), COUNTIFS({Second Sheet Unique ID Column}, @cell = [Unique ID]@row)), [Start Date]@row) - [Start Date]@row

Annaleah Morrow

That is very helpful, Paul. Thank you! I was going the copy route for the baseline too, but hadn't figured out the next step. I appreciate your help.