Locking in a timestamp on status change

I don’t think this is currently possible using native Airtable tools since formulas can’t save state. I made a related product suggestion not long ago. Like all problems where Airtable falls short, there’s always integrations or the API.

So, what I ended up doing was using check boxes. When the box is checked, it adds to a counter which changes the status. The checkbox is then tied to a “date modified” field which marks the time when the status was changed. I have a checkbox for each different stage and then I can use datetime_diff to calculate the elapsed time between each status change.

Nice work. An alternative would be to use date fields in place of the checkboxes and a formula with nested IFs checking for the presence of dates

Hi, I’m new to Airtable but intrigued by this timestamping functionality. I have a workflow with around 9 status’, I would like to lock in separate time stamps when the status is updated to, say, one of 3 states. eg when status is moved to status3, record the date, when status is changed to status4, record the date (in a different field) so, by the end we can track when the various statuses were logged. Would either of the above work for this ?

Thanks in advance

So, do I understand you correctly that there is a single status field with 9 different choices? Let’s say

STATUS can be RED, ORANGE, YELLOW, GREEN, BLUE, PURPLE, VIOLET, CYAN, or MAGENTA.

DATE1 records date/time when the status becomes RED

DATE2 records date/time when the status becomes BLUE

DATE3 records date/time when the status becomes CYAN

So, if my understanding is correct, in my experience, you can’t do this with a single select field for STATUS because every time you change the status, the DATE1 field would change. What I did was create a checkbox field for each status. Basically RED has a checkbox, ORANGE has a checkbox, etc. DATE1 would be a formula field with the following formula:

IF({RED}=TRUE(),(DATETIME_FORMAT(LAST_MODIFIED_TIME({RED}), ‘MM/DD/YYYY HH:mm’)),"")

When RED is checked, it will insert the date and time that it was checked. When RED is unchecked, the date and time will be erased. Does this help?

Hi Ben

Thanks for your speedy response. Yes, you do understand correctly, that is exactly what I am looking to be able to do.I will give this a go and see how I get on. Thanks again for your prompt response and assistance