Help

Static Aging of Status

Topic Labels: Formulas
Solved
Jump to Solution
393 5
cancel
Showing results for 
Search instead for 
Did you mean: 
dani_d
5 - Automation Enthusiast
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
TheTimeSavingCo
18 - Pluto
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?

See Solution in Thread

dani_d
5 - Automation Enthusiast
5 - Automation Enthusiast

Amazing, thank you!!

See Solution in Thread

dani_d
5 - Automation Enthusiast
5 - Automation Enthusiast

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

See Solution in Thread

5 Replies 5
TheTimeSavingCo
18 - Pluto
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?

dani_d
5 - Automation Enthusiast
5 - Automation Enthusiast

Amazing, thank you!!

@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?

Try to look for a record that's linked to the triggering record that has no end date?
Screenshot 2024-06-20 at 11.20.35 PM.png

Screenshot 2024-06-20 at 11.20.40 PM.png

Link to base

 

  

dani_d
5 - Automation Enthusiast
5 - Automation Enthusiast

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