Count Days A Record Has Been in the Same Status

[Molly_Bendell]

Is this possible? We would like to know the ‘aging’ of our status field. Meaning, how long has a record been sitting in the current status. I’ve included a screenshot (VIEW HERE) of our base pointing to the Status field and the ‘Aging’ field we want to populate with this data. We already have a last modified date but that, as you all know, is for ANY change made to a record. We only want ‘Aging’ to track the Status field. Thank you!

[AlliAlosa · Accepted solution]

Hi there!

You’ll want to use something like this…

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

I added the IF() statement because if either of the following things are true…

1. A record has nothing in the {Status} field
2. The status was modified before the LAST_MODIFIED_TIME() formula function was introduced

You’ll get a bogus result like “18136” days, which is almost 50 years ago :slightly_smiling_face:

[Molly_Bendell]

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

[Dillon_Hamm]

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

[Olly_L]

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.

[Thomas_Walker]

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?

[Suzi_Lebin]

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”

[Eric_Petersen]

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.

[Suzi_Lebin]

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.

[Eric_Petersen]

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?