Calculating difference between Dates from the same field

[Dorrie]

Hi all,

The data from the table below is from another table using Automation:

Once the status of a request ID is updated, then the update time of each status will be automatically recorded in this table.

Is there any way to calculate the time difference between each of the statuses and then update the field "Duration"?

I've tried putting the update time of each status in different fields(Image 2), but still failed at getting the duration.

(Image 1) 

(Image 2) 

[Justin_Barrett]

Welcome to the community, @Dorrie! 😁

Formulas run at the record level, meaning that the same formula runs once per record. As the formula is running on any given record, all field references are from the same record. There's no way to reference other records or their field values directly within a formula.

One way around this is to use a linked record field and one or more rollup fields. By linking one record to another in the same table, you can roll up field values from the target record. Once that's done, the value returned by the rollup field can be used in a formula. The challenge then becomes finding the proper record to link to in an automation. The easiest way that I know is to use scripting, but perhaps that's because I'm very comfortable writing code. This would be extremely difficult using non-scripting automation features, but it could probably be done.

That said, there's an easier way, and it doesn't require code.

In the table where the status values are changing (I'll call this the Source table from here on out), do the following:

• Add a last-modified-time field that targets your status field (if you don't have one already). Name it "Update Time"
• Add a date field that includes the time. Name it "Previous Update Time"

In the table where you're adding records to track the status changes (the Destination table going forward), do the following:

• Add a date fields that includes the time. Name it "Previous Update Time"
• Change your "Duration" formula field to the following:

IF(AND({Update time}, {Previous Update Time}), DATETIME_DIFF({Update time}, {Previous Update Time}, "seconds"))

Set the formula field's formatting options to output a duration, using the h:mm:ss option.

The automation that triggers on status changes should do the following:

• Create Record - This action creates a new record in the Destination table, copying the values from the "Request ID" field, the status field, the "Update Time" field, and the "Previous Update Time" field into this new record.
• Update Record - This action updates the "Previous Update Time" field in the triggering record with the value from the "Update Time" field.

That's it! Here's how it looks in my tests after a few status changes. The first line has no duration because it's the first change, the second line indicates that 1 minute and 33 seconds elapsed between First Status and Second Status, and so on.

[Dorrie]

Hi @Justin_Barrett ,

Thank you so much for your response!

[Dorrie]

@Justin_Barrett 

Still got a problem setting up the update record, can you please help me out with a more detailed guide?

[Justin_Barrett · Accepted solution]

@Dorrie For the Update record action, there are only two things to configure:

• The record ID of the record to update, which is the record that triggered the automation.
• Updating the the "Previous Update Time" field with the value from the "Update Time" field, which can be taken from the trigger.

Here's a screenshot of my configuration:

If you're having trouble inserting the update time, click the gear on the right end of the field setup and make sure that it's set to Dynamic. This will let you choose values from previous steps.

If there's something else that's not working, please provide more details.

[Dorrie]

Hi @Justin_Barrett ,

I just successfully got the results I want, thank you!!

[Justin_Barrett]

Glad to hear it, @Dorrie! If you would, please mark my comment (the one above, not this one) as the solution to your question. This helps others who may be searching with similar questions. Thanks!