Help

Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here

Locking in a timestamp on status change

Topic Labels: Formulas
2391 6
cancel
Showing results for 
Search instead for 
Did you mean: 

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!
image

6 Replies 6

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