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.
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).