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!
Best answer by TheTimeSavingCo
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?
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?
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?
@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?
@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?
