Locking in a timestamp on status change

[Ben_Weston]

Hi, I’m trying to lock in a timestamp when a status changes to one particular status. I would like that timestamp to be locked in and not change once the status changes to the next stage.
Here’s a formula that would work except that once the status is changed off of the desired status it wouls go back to blank.
IF({Status}=“Shop Floor”,NOW(),"")
I could do it the following way, but the circular reference isn’t allowed.
=IF({Status}="",(if({Status}=“Shop Floor”,NOW(),{Plan to CNC})))
Anyone have a workaround? Thanks in advance!

[Zollie]

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.

[Ben_Weston]

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.

[Zollie]

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

[David_Cramp]

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

[Ben_Weston]

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?

[David_Cramp]

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