Help

Count Days A Record Has Been in the Same Status

Solved
Jump to Solution
8406 12
cancel
Showing results for 
Search instead for 
Did you mean: 
Molly_Bendell
5 - Automation Enthusiast
5 - Automation Enthusiast

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!

12 Replies 12

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.

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