Thursday, November 27, 2014

calculate Case Age Based On Working (Business) Hours

To track the time spent by an agent on a case taking into consideration the business hours, you will need to request the "Enable Case Duration/Age in Business Hours" feature to be enabled for your org. This will give you a field called "Business Hours Age" that can only be included in your reports.

**NOTE: The "Business Hours Age" field will not be available in reports if the business hours for the Organization is set to 24/7.

If you require information that can be displayed on the case record itself you can create a complex formula. An example of what a formula for CST and standard M-F business hours (these business hours need to be configured under Setup | Company Profile | Business Hours)  is below:

IF(DATEVALUE(CreatedDate) = DATEVALUE(ClosedDate),  
(ClosedDate - CreatedDate) * 24,  
((CASE(MOD(DATEVALUE(CreatedDate) - DATE(1985,6,24),7),  
0 , CASE( MOD(DATEVALUE(ClosedDate) - DATEVALUE(CreatedDate) ,7),1,2,2,3,3,4,4,5,5,5,6,5,1),  
1 , CASE( MOD(DATEVALUE(ClosedDate) - DATEVALUE(CreatedDate) ,7),1,2,2,3,3,4,4,4,5,4,6,5,1),  
2 , CASE( MOD(DATEVALUE(ClosedDate) - DATEVALUE(CreatedDate) ,7),1,2,2,3,3,3,4,3,5,4,6,5,1),  
3 , CASE( MOD(DATEVALUE(ClosedDate) - DATEVALUE(CreatedDate) ,7),1,2,2,2,3,2,4,3,5,4,6,5,1),  
4 , CASE( MOD(DATEVALUE(ClosedDate) - DATEVALUE(CreatedDate) ,7),1,1,2,1,3,2,4,3,5,4,6,5,1),  
5 , CASE( MOD(DATEVALUE(ClosedDate) - DATEVALUE(CreatedDate) ,7),1,0,2,1,3,2,4,3,5,4,6,5,0),  
6 , CASE( MOD(DATEVALUE(ClosedDate) - DATEVALUE(CreatedDate) ,7),1,1,2,2,3,3,4,4,5,5,6,5,0),  
999)  
+ (FLOOR((DATEVALUE(ClosedDate) - DATEVALUE(CreatedDate))/7)*5)  
- 2) * 9)  
+  
MAX((  
DATETIMEVALUE(TEXT(YEAR(DATEVALUE(CreatedDate))) & "-" & TEXT(MONTH(DATEVALUE(CreatedDate))) & "-" & TEXT(DAY(DATEVALUE(CreatedDate))) & " 01:00:00am") - CreatedDate + 1) * 24, 0))  
+  
MAX((ClosedDate -  
DATETIMEVALUE(TEXT(YEAR(DATEVALUE(ClosedDate))) & "-" & TEXT(MONTH(DATEVALUE(ClosedDate))) & "-" & TEXT(DAY(DATEVALUE(ClosedDate))) & " 16:00:00pm")) * 24, 0)

Note: This is only an example and should only be used as a reference. It is not advised that you copy/paste this formula in your environment as different time zones/business hours will make it behave differently. Lastly, this formula may not work properly on List Views/Reports, for reports, use the  "Business Hours Age" field.  

No comments :

Post a Comment

Labels

visualforce page ( 13 ) apex integration ( 5 ) apex trigger ( 4 ) csv file from vf page ( 4 ) javascript ( 4 ) csv visualforce page ( 3 ) Too many ( 2 ) call out ( 2 ) integration ( 2 ) rest api ( 2 ) salesforce rest api ( 2 ) salesforce to salesforce integration ( 2 ) sfdc rest api ( 2 ) trigger ( 2 ) 15 digit to 18 digit ( 1 ) DML rows in Apex ( 1 ) Date Conversion ( 1 ) Date/Time conversion ( 1 ) Deploy ( 1 ) Objects to Future Annotated Methods ( 1 ) SFDC limits ( 1 ) Sobject to Future Annotated Methods ( 1 ) Test Class ( 1 ) TimeZone Conversion ( 1 ) Too many dml rows ( 1 ) Too many future calls ( 1 ) annotations ( 1 ) apex code ( 1 ) closed opportunities ( 1 ) commit ( 1 ) convert ( 1 ) create records ( 1 ) csv create records ( 1 ) custom setting ( 1 ) deployment ( 1 ) deployment changeset ( 1 ) disable apex class ( 1 ) disable apex trigger ( 1 ) disable in production ( 1 ) document ( 1 ) download ( 1 ) field name ( 1 ) formula fields ( 1 ) iframe ( 1 ) inactive ( 1 ) intellisense ( 1 ) jsforce ( 1 ) limits ( 1 ) matrix report in vf page ( 1 ) multi select ( 1 ) multi select salesforce ( 1 ) multiselect ( 1 ) paypal ( 1 ) picklist ( 1 ) record type ( 1 ) rollback ( 1 ) salesforce limits ( 1 ) salesforce list ( 1 ) salesforce map ( 1 ) salesforce rest ( 1 ) salesforce set ( 1 ) salesforce1 ( 1 ) sandbox deployment ( 1 ) sfdc collection ( 1 ) sfdc list ( 1 ) sfdc map ( 1 ) sfdc rest ( 1 ) sfdc set ( 1 ) uncommitted ( 1 ) updated field ( 1 ) user ( 1 ) validation rule opportunity ( 1 ) validation rules opportunities ( 1 ) vf page ( 1 )

Ad