Calculate date/time difference between Status change log entries

[RP1]

I created an automation to log the date/time for every status change in a given project.

There are 4 status options:

* Todo
* In Progress
* On Hold (waiting for information from external source)
* Done

My goal is to calculate the hours spent actually doing the project, which would always have "In progress" as the start date. See screenshot below.

The tricky part is that the projects always have a back and forth between status: (...In progress -> On Hold -> In progress -> Done...)
Would it be possible to get the difference between "In Progress" and the next status?

I found this 5 year-old thread that tackles a similar problem, but it's not quite the same thing I'm trying to achieve here.

https://community.airtable.com/t5/other-questions/subtracting-last-two-created-linked-record/td-p/51...

[Stephen_Orr1]

Hi @RP1,

Because Airtable tables do not have an inherit row order and it’s possible to have multiple tasks occur on the same date, you’ll need to either

• create a end date field and use datetime_diff() or workday_diff() between these two dates
• create a linked record field pointed at the same table, populate it with dependencies, use a roll-up or lookup + formula field on the dependent task to get the difference 

Hope that helps

Stephen

[RP1]

Thank you for the input.

The end date would be the date of the next status log.
I figured that the solution would involve lookup/rollup but I don't know how to put it together.
I've done a fair amout of lookups before, but rollups are new to me.

If anybody has an idea it would be much appreciated.

[Stephen_Orr1]

In order to use a lookup or roll up, you will need a linked record field pointed at the same table. You can name this linked record field "Predecessor Status Log" and create an automation that inserts the last available record (based on date) into this field. Then you can create a lookup on this field to get the "Predecessor Status Log Date" as field on the same record to use in a datetime_diff() formula field.

[RP1]

I accepted as a Solution by accident.

I get this part:

"In order to use a lookup or roll up, you will need a linked record field pointed at the same table. You can name this linked record field "Predecessor Status Log" (...)
(...)Then you can create a lookup on this field to get the "Predecessor Status Log Date" as field on the same record to use in a datetime_diff() formula field.

The automation is the problem:
(...)and create an automation that inserts the last available record (based on date) into this field. (...)

I thought about doing something like:

When a record is created in Log
Update a record
Table: Log
Record ID: Airtable Record ID
Fields: Predecessor Status Log Date

It didn't work. I'm sure missing something.

[Stephen_Orr1]

I think I might have missed that Find Records has a limit of 100 records and that Airtable does not allow conditions in repeating group statements. In that case, either this will require a script to get the max date from all records or every log status needs to be linked to the same record in a parent table where you can use a roll up to get the max date and then use this in the automation. Apologies for the change.