Help

How to set a date based on an option in a single select field

Topic Labels: Formulas
3177 4
cancel
Showing results for 
Search instead for 
Did you mean: 
Melisa_Goh
5 - Automation Enthusiast
5 - Automation Enthusiast

I’m using a single select field to note the different statuses in a production workflow. I’d like to create a field to mark the date that the record was set to a particular status, “Ready to Publish,” and keep that date persistent even when the status moves on to another stage.

I started with this formula, which does return the date something is set to the correct status:

IF(Status=“Ready to Publish”, TODAY(), “”)

But then goes blank when the status updates to the next stage. How can I make the date persistent after the status changes? Thanks for any help.

4 Replies 4

This isn’t possible directly within Airtable. You could use an integration service like Zapier or Integromat to do the job, though. My gut says that Zapier would be the easier of the two in this case.

In Airtable, create a view with a filter that only shows records with the status “Ready to Publish”. In Zapier, make a new zap using the Airtable “New Record in View” trigger, and target that “Ready to Publish” filtered view you just made.

For the Zapier action, use the Airtable “Update Record” action, and tweak its settings to update the same record that triggered the zap, adding the current date to the appropriate field.

Repeat this for all other statuses that you want to track. Each will have its own date field, its own view, and its own zap to update that date field.

Hello, I have the same problem as OP and I’m new to zapier. Could I bother you with a more detailed explanation on how to set up the “update record in airtable” step? I’m having trouble trying to select the same record that triggered the zap.

Welcome to the community, @Mario_Alberto_Duque! :grinning_face_with_big_eyes: I must admit, the process for picking that triggering record isn’t very intuitive. When setting up the “Update Record in Airtable” step, you need to choose “Use a Custom Value (advanced)” for the Record option. That will let you choose the appropriate data from the first step, which is going to be the “Record id” value:

Screen Shot 2020-02-29 at 7.24.18 PM

If the date is all you care about, put {{zap_meta_human_now}} into the date field, and Zapier will insert the current date when the zap runs. If you also want to track the time, I suggest using one of the specific timezone-based options listed on this page, making sure that the GMT option is disabled in the Airtable date field you wish to update.

However, be aware that Zapier won’t trigger the zap instantly, but on whatever interval is allows by your account. So if it runs every 15 minutes, for example, the recorded time won’t be the exact time you changed the status, but the time that Zapier runs the zap.

Thank you so much Justin! I tried this and seems to be working fine :grinning_face_with_big_eyes: