This article is a guide for how to track the amount of time a record maintains a specific status. This can be helpful for teams who need to stay on top of how long a record stays in each stage of a process. 

Introduction

In this article we'll use a scenario where a content marketing team wants to track how long blog articles in their development queue spend in each stage of their process. The end result is valuable data that can provide insights into their internal processes. Focusing in on new blog content, each new article can move through five different stages: 

1. Planned
2. Assigned
3. Review
4. Staging
5. Published

Designate a status field

Use a single select field to designate each of the different statuses. 

Create date fields to timestamp each status change

To timestamp when a record changed to each unique status we need to create four date fields; one each for {Planned}, {Assigned}, {Review}, {Staging}, and {Published}. These fields will be populated later by the automation. 

Create a last modified time field

The last modified time field will be used by the automation to timestamp each of the four date fields we just created. After you add the last modified time field, make sure to select the "Specific fields" option and select the {Status} field. This will ensure that the last modified time is only displayed when that specific field changes. 

Looking to just track one particular value in a certain field?

If you are looking to just track updates in certain fields for certain values, then building out a formula using the LAST_MODIFIED_TIME() function alongside conditional IF() statements will likely be a better choice than using the Last Modified Time field. 

For example, using our Content calendar template you might want to be notified whenever content goes live. So we can build out a formula field that only returns the date and time when the Status field is set as "Live".

Configure automations

Now that the required fields have been configured we can start adding the automations. We'll create five automations that will each update one of the date fields. Follow the steps below to create the first automation. 

Choose an automation trigger

Start by adding an automation and selecting the "When record matches conditions" trigger. 

Then configure the trigger to run only when the status has the value "Planned". 

Finally, test your trigger before moving on to the next step. 

Add an automation action

Click the "Add action" button and select the "Update record" action for Airtable. Select the same table that was used in the trigger, and then add the record ID (which is populated from the trigger). 

Under "fields" choose the {Planned} field, then add in the value from the {Last Modified Time} field.

Your action settings should now look like the example below. Be sure to test your action step as well before turning on the automation. 

Duplicate automations

To timestamp each date field we'll need to have one unique automation per date field. Since the first automation is already configured we can quickly duplicate it and adjust the values in order to update each of the four fields.

In the example below, we'll start by duplicating and renaming the automation. 

Next, adjust the trigger condition to update only when the {Status} field has the "Assigned" value. 

Lastly, adjust the action to use the {Assigned} field instead of the {Planned} field. Make sure to add the {Last Modified Time} field value back in as well. 

Repeat these steps four more times and adjust the settings accordingly to create automations for the remaining date fields.

Once all five automations are configured, each date field will be timestamped whenever its corresponding status is changed. For example, when a record changes status from Staging to Published, the Review field will be timestamped with the exact date and time the status was changed. 

Use a formula to calculate status duration

IF(
  AND(
    {Planned}, {Assigned}
  ),
  DATETIME_DIFF(
    {Assigned},
    {Planned},
    'hours'
  )
)

IF(
  AND(
    {Assigned}, {Review}
  ),
  DATETIME_DIFF(
    {Review},
    {Assigned},
    'hours'
  )
)

Now you should have four separate formula fields that each output the amount of hours a record spent in each stage of the content process.