Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Aug 28, 2019 12:26 PM
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!
Solved! Go to Solution.
Aug 28, 2019 12:57 PM
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…
You’ll get a bogus result like “18136” days, which is almost 50 years ago :slightly_smiling_face:
Aug 28, 2019 12:57 PM
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…
You’ll get a bogus result like “18136” days, which is almost 50 years ago :slightly_smiling_face:
Aug 29, 2019 06:41 PM
Thank you so much!! That worked exactly as intended. Appreciate your help!
Jan 07, 2020 09:09 AM
I was wondering if there is a way to only count work days in this formula?
Jan 07, 2020 12:57 PM
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.
Sep 08, 2020 12:38 PM
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?
Sep 15, 2020 01:26 PM
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”
Oct 26, 2020 12:32 PM
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.
Oct 26, 2020 12:49 PM
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.
Oct 27, 2020 07:16 AM
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?