Count Days A Record Has Been in the Same Status

Thank you so much!! That worked exactly as intended. Appreciate your help!

I was wondering if there is a way to only count work days in this formula?

Yes, Airtable has a formula to calculate the number of work days between two dates.

IF( LAST_MODIFIED_TIME(Status), WORKDAY_DIFF( TODAY(), LAST_MODIFIED_TIME({Status}) ) )

By default the WORKDAY_DIFF formula only counts weekdays, but you can also add holidays into the function if desired.

IF(LAST_MODIFIED_TIME(Status), DATETIME_DIFF(TODAY(), LAST_MODIFIED_TIME({Status}), 'days'))

Hey there!

I noticed you did something on count days a record has been in the same status…

I’m looking to do something similar, but rather, count the amount of time records stay in between statuses.

For context, my base is to run an editorial operation, and what I’m ultimately looking to see how long it takes for one of my editors to go from “for review” to “publish” (or probably, more specifically, the different statuses in between those two states)

Is this possible?

I am looking for something similar. I need to track the amount of time a record takes to move through our process when something changes from “under review” to “feedback sent” to “response received” to “finalized”

Hi Suzi,
Were you able to come up with a solution to track the amount of time a record goes through each stage of your process? I am thinking you would create formula fields for each stage you want to track.

I created a new table and used an automation to create a new record each time the record changes status. The new record will have report name, the status and the date it changed to that status. I have not been able to figure out a formula that would work.

I like your new table solution with an automation and your use of Grouped Records with Date Range (days).

Community, Now to solve the original question on this thread, how do we calculate the number of days the record was in each stage?

You can add a new linked record field to the table of status changes. The new linked record field would link to the next stage (when there is one). Then use a lookup field to of the next stage’s start date/time as the current record’s end date/time. Finally, use DATETIME_DIFF in a formula field to calculate the duration of the stage.

To manage the linked records with the automation, you need to keep track of the record for the most recent stage. So the project table needs a new linked record field that will be managed by the automation.

Actions for the automation:

1. Create a new status record, linking it to the project and setting the status to the project’s status.
2. Get the previous linked status record from the project and set it’s “next status” linked record field to the newly created status record.
3. Update the project record’s “last status record” to the newly created record, so that it will be ready for the next status change.

I usually build automations like this with scripting, so I can do different things if there are missing inputs.

However, this is also possible to create without scripting. Without scripting, you would need to have two different automations. One automation would perform the steps outlined above. The other automation would leave out the second set for the first stage, as there would be no previous stage. Use the “when record meets conditions” trigger to differentiate between the two.

IF(LAST_MODIFIED_TIME(Status), DATETIME_DIFF(TODAY(), LAST_MODIFIED_TIME({Status}), 'days'))

Hi!

Could you tell me how to modify this formula so it includes only workdays?

Thank you!

Olly,

This is the exact type of formula I'm looking for, but it doesn't seem to be working in my base when I copy it in. It always populates negative values (even ones beyond just weekends, so -39 in some cases).