Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Jan 24, 2020 01:51 PM
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!
Jan 31, 2020 11:31 AM
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.
Jan 31, 2020 12:40 PM
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.
Jan 31, 2020 02:22 PM
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
Apr 19, 2020 10:05 AM
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
Apr 19, 2020 12:56 PM
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?
Apr 20, 2020 01:19 AM
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