Airtable Cobuilder is here! Learn more about our new no-code app creation feature, powered by AI on the Airtable Academy

# Re: Static Aging of Status

Solved
Jump to Solution
554 0
cancel
Showing results for
Search instead for
Did you mean:
5 - Automation Enthusiast
I am trying to create a formula to calculate the number of days a specific status is active.
For example, how many days the ticket is "On Hold".

The need is to calculate the number of days the ticket was actually active, in other words the difference between the {Closed Date} and the {Created Date}, minus the number of days the ticket was in a "On Hold" status.

However, I am not finding a way to keep the counter, when the status changes. Currently, the counter resets to 0 when the status changes.

If you could advise me on how to do this, that would be great!
3 Solutions

Accepted Solutions
18 - Pluto

You'll need some automations to help with this I'm afraid.  Assuming a ticket can only ever become on hold once, try:
1. Create a date field called "On Hold Start Date"
2. Create a date field called "On Hold End Date"
3. Create a "Last Modified Time" field that points at the "Status" field
4. Create an automation that will trigger whenever the "Status" field is updated and give it some conditionals:
- Conditional 1: If Status = "On Hold", use an "Update Record" action to paste the "Last Modified Time" value into the "On Hold Start Date" field
- Conditional 2: If "On Hold Start Date" is not empty AND Status != On Hold, use an "Update Record" action to paste the "Last Modified Time" value into the "On Hold End Date" field

You can now use a formula field to calculate the difference between the two On Hold Start and End date fields to get the number of days it was on hold

If a ticket can go into an "On Hold" status multiple times I think you'd need to create a new table to handle it, and we'd create a record in there each time a ticket entered an On Hold status, does that make sense?

5 - Automation Enthusiast

Amazing, thank you!!

5 - Automation Enthusiast

This was perfect, exactly what I needed! Thanks!!

5 Replies 5
18 - Pluto

You'll need some automations to help with this I'm afraid.  Assuming a ticket can only ever become on hold once, try:
1. Create a date field called "On Hold Start Date"
2. Create a date field called "On Hold End Date"
3. Create a "Last Modified Time" field that points at the "Status" field
4. Create an automation that will trigger whenever the "Status" field is updated and give it some conditionals:
- Conditional 1: If Status = "On Hold", use an "Update Record" action to paste the "Last Modified Time" value into the "On Hold Start Date" field
- Conditional 2: If "On Hold Start Date" is not empty AND Status != On Hold, use an "Update Record" action to paste the "Last Modified Time" value into the "On Hold End Date" field

You can now use a formula field to calculate the difference between the two On Hold Start and End date fields to get the number of days it was on hold

If a ticket can go into an "On Hold" status multiple times I think you'd need to create a new table to handle it, and we'd create a record in there each time a ticket entered an On Hold status, does that make sense?

5 - Automation Enthusiast

Amazing, thank you!!

5 - Automation Enthusiast

@TheTimeSavingCo  - So the ticket can go into an "on hold" status multiple times. I've managed to create a new record in a separate table with the date the on hold was created, each time the "on hold" status was chosen. However, I am struggling to figure out how to associate the "on hold end date" field with the same record, when the status changes from "on hold" to something else?

I would need this to calculate the number of days each time the ticket was on hold. Unless I am missing something?

18 - Pluto

Try to look for a record that's linked to the triggering record that has no end date?

Link to base

5 - Automation Enthusiast

This was perfect, exactly what I needed! Thanks!!