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.

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.

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 (or TODAY() or NOW()) alongside conditional IF() statements will likely be a better choice than using a Last modified time field.

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

IF({Status}="Live", TODAY())

You'll still need to create an automation that is similar to the steps in the next section. One major difference is that you'll likely want to use the "When record matches conditions" trigger in the automation. In this case, you'd set it to only trigger the timestamp to be made when the status is changed to "Live."

Remember that the time-based formula you are creating here will update over time. In most cases, this formula can be hidden in the view as its purpose is only to be referenced 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 current value from the "Last modified time" field we set up earlier.

   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. Finally, click the cogwheel so that the value can be filled dynamically with whatever the current value of the "Last modified time" field is showing.⁴
      

1. 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.
   

2. 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."
   

3. 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.
   

4. 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 the status was changed.

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}