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?
Oct 29, 2020 12:16 PM
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:
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.
Feb 27, 2023 12:27 PM
Hi!
Could you tell me how to modify this formula so it includes only workdays?
Thank you!
Feb 27, 2023 12:28 PM
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).