Calculating difference between Dates from the same field

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:

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.

Hi @Justin_Barrett ,

Thank you so much for your response!

@Justin_Barrett 

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

Hi @Justin_Barrett ,

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

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!