function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
leah brooks 11leah brooks 11 

Case Timer

Hello,

I am trying to set up an equation that will output the TOTAL amount of time that a case has been in 'On Hold' status.  I was able to figure out how to get the case to show the first 'on hold' status length, but after the status has been switched to, say, 'Working' and then back to 'On Hold' I need to be able to add on the new on hold status to the old.  The goal is to have a more accurate case time.

In a nutshell (total case open to complete time - on hold time = total working time)

I already have the below formula that outputs total case hold to complete time as a decimal:

Case_Open_Length__c =
BLANKVALUE(Complete_Date_Time__c, NOW()) - CreatedDate

I need a way to calculate the total hold time however, so that I can make an equation to factor it out.

Thank you in advance for any help!
Best Answer chosen by leah brooks 11
leah brooks 11leah brooks 11
Thank you for your response, I did get it to work!  I wound up using your idea to create workflows which helped out immensly.

What I wound up doing:  I created two custom, hidden field for the case object.  One to drop the Current Hold Time whenever a case went into hold status, and the other to hold Total Hold Time.

I then made three different workflow forumulas.  The first one simply took the current date/time whenever a case was put on hold, and dropped it into the Current Hold Time Field.  The second workflow took the current date/time whenever a case was taken OFF of hold status, subtracted the value in the Current Hold Time field from it, and put the results into the Total Hold Time Field.  The last workflow I made did a very similair process, but it also added on whatever was currently in the Total Hold Time field (i.e. if the case had been on and then off hold and was now on it again).  So basically it checked if there was already a value in the Total Hold Time field, and if there was it subtracted Current Hold Time from NOW () and then added on whatever value was already in Total Hold Time.

It is working great, thank you for your suggestion to use workflows, sometimes I just need a kick in the right direction! ;)

All Answers

Rahul BoinepallyRahul Boinepally
Create a formula field of type Date - 'Last_Hold_date', which is populated with current date when case status is changed to 'On Hold' and previous status is not 'On Hold'.

Create a new custom field say 'Temp_Hold_Time' which is not visible to any page layouts - to store the hold times. 
workflow field to be evaluated when:

AND( ISCHANGED(STATUS) , PRIORVALUE(STATUS) = 'On Hold' , NOT(ISPICKVAL(STATUS) ,  'On Hold')) - somthing like this. 


Workflow field update:

Temp+hold_time +  (Last_Hold_Date - NOW() )*24*60)

I have not tested this, but let me know if you get any issues or further detail.
 
leah brooks 11leah brooks 11
Thank you for your response, I did get it to work!  I wound up using your idea to create workflows which helped out immensly.

What I wound up doing:  I created two custom, hidden field for the case object.  One to drop the Current Hold Time whenever a case went into hold status, and the other to hold Total Hold Time.

I then made three different workflow forumulas.  The first one simply took the current date/time whenever a case was put on hold, and dropped it into the Current Hold Time Field.  The second workflow took the current date/time whenever a case was taken OFF of hold status, subtracted the value in the Current Hold Time field from it, and put the results into the Total Hold Time Field.  The last workflow I made did a very similair process, but it also added on whatever was currently in the Total Hold Time field (i.e. if the case had been on and then off hold and was now on it again).  So basically it checked if there was already a value in the Total Hold Time field, and if there was it subtracted Current Hold Time from NOW () and then added on whatever value was already in Total Hold Time.

It is working great, thank you for your suggestion to use workflows, sometimes I just need a kick in the right direction! ;)
This was selected as the best answer
Rahul BoinepallyRahul Boinepally
Great, please mark it as solved.
Sumit Rana 1Sumit Rana 1
HI , Can anyone solve my query pls. its similar to the above query. I have to get te exact time someone has worked on the case. 

So i had made 2 date/time fields. start date & on hold. If case status= In progess, start date should gets updated & when status = hold, on hold gets updated. 

Now, there are 2 formula fields, Actual time & time tracker.(both number type).

Actual time: calculates the time diff of both date/time fields & output that in minutes (via subtracting)

& time tracker is to hold the value of actual time & add everytime someone works via making status= in progress & stops at on hold. Then after sometime if anyone again starts the work by changin status to in progess, it sould again add up the timw until that user changes the status to on hold.

Now the time tracker is getting -ve values as sometime on hold- start time is bigger. So i am not able to hold te prevbious value & adds up to it. It just give me te current value which is not I want.

Can anyone help pls??