Print this page

How to calculate Case Age Based On Working (Business) Hours

Knowledge Article Number 000089863
Description
Is there a way to track the time spent by a support agent on a case taking into consideration the business hours?
Resolution
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:00")) * 24, 0)

You can also explore our AppExchange website for other possible solutions: AppExchange.

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. 

See also:
Date formula on report shows "#Error!" while working on the record in the user interface?




promote demote