Time-stamping a date that can be changed


#1

Hello community,
I have seen a few topics similar to the one above (Last Modified Timestamp) and (Most Recent Date from another table).

I am using this for project management. I want to see either:

  • How many times the “Due Date” field has been updated
  • Or the difference in the original date set and the final date when “Status” is marked as complete

The use case is to measure our velocity to see how quickly we are accomplishing activities but also keep the “Due Date” updated properly so we can see when things are going to be done.

I haven’t been able to figure out a way to do this effectively. Any help would be great!


#2

We share this exact need. Resource management requires the ability to make calculations against state-change, not just state. Knowing the duration between two states would be a HUGE win, particularly if a record of state-change was made per field and could be queried via formula.


#3

Currently, the only Airtable field with persistence is CREATED_TIME(). Accordingly, the only real way to keep track of changes to {Date Due} — that is, the only cybernetic, rather than procedural, way — would be to make {Date Due} a linked-record field. You could then use a roll-up field with a MAX(values) aggregation function to identify the last date set. (That assumes {Date Due} is only changed to a later date; if it can go both ways, there’s still a way to determine it, based on CREATED_TIME() of the last change.) This would give you a count of how many times {Date Due} has been changed and the date and time of each change.

As I recall, I used this technique in tracking what I call ‘outs’ in my Wardrobe Manager base. Since an item can be sent out (for cleaning, repair, or to be used) multiple times, I needed a way to reflect that. My use is a little more complex than yours, as I needed to track status, due date, and restoral date and reason for each out, but the basic code should be similar.