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.
In this article we'll be showcasing the "When record matches conditions" trigger and the "Update record" action.
IN THIS ARTICLE
Designate a status fieldCreate date fields to track each timestamp
Create a last modified time field
Configure automations
Use a formula to calculate status duration
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:
- Planned
- Assigned
- Review
- Staging
- Published
NOTE
Automations are helpful in this scenario because it can be used to input a static date and time into a date field based on when the status field changes. This would otherwise not be possible without leveraging a custom API call or a third-party tool like Zapier.
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.
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. The "Update record" action works by identifying a single record to update, via a record's ID, and then specifying how that record should be updated. In this example we will use the ID of the record that triggered this automation(the record from our trigger step).
Select the table where the record you'd like to update is located, and then add the record ID for the single record you'll be updating.
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 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'
)
)
