Use automations to timestamp status updates

Use a single select field to designate each of the different statuses. In our example, we call this the "Status" field, but you might name this something else depending on your individual workflow's needs.

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

Start by navigating to the Automations section in your base located in the top left part of the screen. Then click Create automation + to begin the setup process.

1. Select the When record updated trigger.

2. Next, choose the name of the table you want the trigger to reference. In our case, this will be the "Content production" table.

3. Then, configure the trigger to only watch for when the "Status" field has been updated. You can skip the optional view selection or Select a view if you only want this automation to be scoped to activity in a certain view.

4. Test the trigger before moving on to the next step.

1. Click + Add advanced logic or action, then choose the Conditional logic option.

2. This will open up a menu to configure the first conditional group. This first group will be set up to only run the action we set next when the "Status" field is set to the "Planned" status option. Then, click the + Add action option.

3. Now, we want to set up the action so that the record will be automatically timestamped with the datetime value when the automation was run.

   1. First, choose the Update record option from the list of actions.

   2. Next, choose the same table from the trigger step, which in our use case is called "Content production."

   3. Then, you'll want to set the Record ID to be the same record from the trigger step. Click the blue plus sign button to insert that value from the token picker.

   4. Next, under Fields you'll click the + Choose field option and select the "Planned" date field.

   5. Finally, click the cogwheel icon. Then, click the Dynamic option. Next, click the blue plus sign button. Here, you will select the A specific time option and then click the Actual run time token. This is the run time of when the automation was triggered to run.

4. You’ll need to repeat this for each of the 4 additional statuses/status date fields in order to timestamp each date field as the content production status changes. To timestamp each of the status date fields, we'll need to have one unique conditional group per status date field. Since the first conditional action group is already configured we can quickly duplicate it and adjust the values in order to update each of the last four status date fields.

   1. After duplicating the conditional group, click into that group and then click Edit conditions to change this conditional group to run the update record action when the "Status" field is marked as "Assigned."

   2. Last, click into the Update record action. You'll need to delete the previous group's "Status" field and follow the same steps to dynamically add the "Last modified time" to update the corresponding status date field.

   3. Repeat these steps three more times and adjust the settings accordingly to create conditional action groups for the remaining status date fields.

Once all five conditional automation actions 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 "Published" date field will be timestamped with the exact date and time when the automation was triggered to run.

Here’s a video to help assist you with setup:

Be sure to test the automation you’ve configured and turn it on when you are ready. Then, monitor the automation behavior in real time in your base. We recommend creating a test record (as long as your base doesn’t have other dependencies to consider) to try out different conditions related to the automation you configured.

To start, add a new formula field. Then copy in the formula below (making adjustments for field names if yours are named differently).

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

The formula will check to make sure that both the {Planned} and {Assigned} fields have a date entered, and if they do, it will calculate the time difference between those dates in hours. If you would rather use a different time output, then check out our article on unit specifiers for DATETIME_DIFF() formulas here.

To get the difference in time for the other stages, duplicate the formula field and adjust the values for each of the different statuses. For example, the next time difference to calculate is the time between "Assigned" and "Review", so the adjusted formula will look like this:

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

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

You can also calculate the total time spent from "Planned" status to "Published" status by creating another formula field that sums together the time values from each of the four fields that you just created. That formula could be as simple as:

{Planned to Assigned}+{Assigned to Review}+{Review to Staging}+{Staging to Published}

The “Actual run time” token is formatted in ISO to correspond with the way date fields are formatted on Airtable’s backend. However, the ISO time should be converted to the timezone of the user looking up the record’s timestamped date.

Just a reminder that the date field being used may or may not show time depending upon the configuration of the date field.

If time hasn’t previously been toggled on in a date field that is updated by an automation’s “Actual run time” token, then no time values can be passed via the automation run. In these instances, when time option is toggled on in a date field, the time will default to midnight / 12:00 am.

If time has previously been toggled on, then the automation will pass the time value onto the date field being updated.