Starting November 20, the site will be set to read-only.
On December 4, 2023,
Apex Code Development (90768)
General Development (55146)
Visualforce Development (37251)
Lightning (18265)
APIs and Integration (17146)
Trailhead (11680)
Formulas & Validation Rules Discussion (11337)
Other Salesforce Applications (8116)
Jobs Board (6655)
Force.com Sites & Site.com (4842)
Mobile (2694)
You need to sign in to do that
Don't have an account?
NicoleB I'm trying to create a formula to determine the average case response time for our service center. We use email to case so I need to determine the amount of time from when the case is created until the initial email response is sent.
Thanks in advance for any help you can provide.
Hi, this is something I have done in the past. I have had code attached to the email to trigger a change in status of the case... Initial Response etc.. based on this I have used a workflow field update to record the time between this status change and the case open date time.
I have extended the functionality of cases, to record true APT (Actual Processing Times) , which is a common metric used in Call Centres.
Please let me know if you require any further information. you can email me on sgare@astadia.com
While calculating the "time", would you be referring to the Business Hours defined for your Org or would you be simply going by the case age as per the calendar days.
In case you donot opt for business hours, you can achieve your requirement through standard configuration using WorkFlows and Formula fields; however, in case of business hours, you will need custom coding in the form of Apex Triggers because WF's or Formula field do not reference the business hours table.
I have just put this in place on our Enterprise setup. It's not perfect but it is a start which I will continue to refine.
Step 1 - create the workflows to calculate the first response and have a field on the case to show this (in my example I used the field First_Response__c)
Step 2 - create a custom formula to calculate the number of hours between the CreatedDate and the First Response.
/*First calculate if first response is on the same day as case created*/
if(((datevalue(First_Response__c) - datevalue(CreatedDate) - 2*( floor((datevalue(First_Response__c) - DATE(1985, 6, 24))/7) - floor ((datevalue(CreatedDate) - DATE(1985, 6, 24))/7)))=0),
/*If response is same day as created date calculate number of hours between the two. */
(round( (First_Response__c - CreatedDate),6)*24),
/* Otherwise work out if case is created on a Friday */
if ((MOD(DateValue(CreatedDate) - Date(1900, 1, 6), 7)=6),
/*Then calculate the total humber of hours between the created and first response*/
(round((First_Response__c - CreatedDate),6)*24)
/*Then take off the of working days between the Created Date and First Response */
- ((datevalue(First_Response__c) - datevalue(CreatedDate) - 2*( floor((datevalue(First_Response__c) - DATE(1985, 6, 24))/7) - floor ((datevalue(CreatedDate) - DATE(1985, 6, 24))/7)))
/*Multiply by the number of non working hours between days (in our case 13)*/
*13)
/*and take off another 48 hours for Sat & Sun*/
-48,
/*If not a Friday do the same as before but do not remove the 48 hours*/
(round((First_Response__c - CreatedDate),6)*24)
- ((datevalue(First_Response__c) - datevalue(CreatedDate) - 2*( floor((datevalue(First_Response__c) - DATE(1985, 6, 24))/7) - floor ((datevalue(CreatedDate) - DATE(1985, 6, 24))/7)))
*13)
))
There are some limitations which I will try and refine over time such as it is inaccurate if case is created at a weekend, should be easy fix but rare in our business. Hope this helps anyone else as it has taken me a number of hours to get to this.
Hi,
I am trying to implement the same thing in my org, but cant figure out the workflows. Any Ideas? This is business critical and so far the simplest idea outside of custom coding. Please let me know at your earliest convenience.
I am in the same situation as DevilDog...needed this yesterday and can't figure out the workflows necessary to accomplish the capturing of the "First Response". How do you determine what the "First Response" is and via what documentation?
Thank you for any input you can provide!
Can you explain what exactly you're looking for? Do you just need a Formula(Number) that displays the amount of time between weh a case is opened, and when a case is closed? Or do you need something more precise that omiting non-business hours, weekends, holidays, etc.
I can't help you with those, but I can create a simple Formula or WFR for you.
Can you explain what exactly you're looking for? Do you just need a Formula(Number) that displays the amount of time between weh a case is opened, and when a case is closed? Or do you need something more precise that omiting non-business hours, weekends, holidays, etc.
I can't help you with those, but I can create a simple Formula or WFR for you.
Thanks Steve. I'm starting easy...just need to know the time between case create date and first response to client (either via returned email or other TASK such as phone). If I can get actual business hours great!
I think I might have figured it out. IF you are using Email to Case, in workflows you can build a workflow from the Email Message object.
1. Only when a record is created
2. Email Message: Status equals Sent
3. (Optional, probably recommended) - Email Message: Subject contains First Response (This can be done in the template used to follow up and ensure your First Response time is only updated once)
4. Field Update (Depending on you having implemented the formula posted above(I called mine Fist Response Time) and you have a custom Data/Time field called "First Resopnse") - Field to update - First Response - Default Value of NOW()
I hope this helps, all initial tests worked beautifully. Unfortunately, this only works if you have Email To Case Enabled. Anything outside of that will probably need some code work.
I really appreciate it. I'm going to give it a try, however, fingers are crossed because, although we use Email2Case, we don't have E2C enabled (figure that one out! :) ). I'll let you know if successful.
In addition, you can also restrict the First Response from being updated mulitple times when an email is sent, by adding Case; Status equals New and adding a field update that updates the Case; Status field to "Updated" , or "Working", or a custom value, ensuring you get the most accurate time stamp for your First Response. Let me know how it goes!
Using your guidance, I was able to get it all in place and almost working. I have one issue that I can identify but don't know a way around it without some big process change.
We have the Account: Account name as the company name in our case record. How do I create this workflow using account fields with email message object? I need to limit a workflow for only certain companies and I dont' have the option of selecting the Account: Account Name field to the Email Notification workflow, just the Case: Account Name (which we don't use).
RMMike,
When I put your string together, I'm getting an error message that reads "Error: Incorrect number of parameters for function 'if()'. Expected 3, received 4". The following is the string. Do you mind reviewing and letting me know where I have erred (I don't see it)? Thank you so much!!
if(((datevalue(First_Response__c) - datevalue(CreatedDate) - 2*( floor((datevalue(First_Response__c) - DATE(1985, 6, 24))/7) - floor ((datevalue(CreatedDate) - DATE(1985, 6, 24))/7)))=0), (round( (First_Response__c - CreatedDate),6)*24), if ((MOD(DateValue(CreatedDate) - Date(1900, 1, 6), 7)=6), (round((First_Response__c - CreatedDate),6)*24), - ((datevalue(First_Response__c) - datevalue(CreatedDate) - 2*( floor((datevalue(First_Response__c) - DATE(1985, 6, 24))/7) - floor ((datevalue(CreatedDate) - DATE(1985, 6, 24))/7)))*13)-48,(round((First_Response__c - CreatedDate),6)*24)- ((datevalue(First_Response__c) - datevalue(CreatedDate) - 2*( floor((datevalue(First_Response__c) - DATE(1985, 6, 24))/7) - floor ((datevalue(CreatedDate) - DATE(1985, 6, 24))/7)))*13)))
IF Statements require an odd number of parameters, you need to ad an ELSE result to your formula. In other words you have to tell the formula what to do if it has evaluated everything thing you've told it to do and it still can't find a match.
Apologies Bartman for the delay but here is the full string that we have in our system:-
if(((datevalue(First_Response__c) - datevalue(CreatedDate) - 2*( floor((datevalue(First_Response__c) - DATE(1985, 6, 24))/7) - floor ((datevalue(CreatedDate) - DATE(1985, 6, 24))/7)))=0),
(round( (First_Response__c - CreatedDate),6)*24),
if ((MOD(DateValue(CreatedDate) - Date(1900, 1, 6), 7)=6),
(round((First_Response__c - CreatedDate),6)*24)
- ((datevalue(First_Response__c) - datevalue(CreatedDate) - 2*( floor((datevalue(First_Response__c) - DATE(1985, 6, 24))/7) - floor ((datevalue(CreatedDate) - DATE(1985, 6, 24))/7)))
*13)
-48,
(round((First_Response__c - CreatedDate),6)*24)
- ((datevalue(First_Response__c) - datevalue(CreatedDate) - 2*( floor((datevalue(First_Response__c) - DATE(1985, 6, 24))/7) - floor ((datevalue(CreatedDate) - DATE(1985, 6, 24))/7)))
*13)
))
Hope this helps.
Mike
Thanks, Mike.
I tried this formula and had errors. I am trying to create 2 fields: 1) "First Response" (Date/ Time field) - this will show when the first email is sent from the support agent to the customer (not counting the auto-response) and 2) "First Response Time" (Number field) - this should calculate the time difference between the First Response and Date the Case was Created (excluding weekends).
I have created both fields but have run into formula errors. Any help is much appreciated - thanks so much in advance!