Many companies want to track how long their cases take to be resolved – and they should! Providing great service should be on the top of the list for every company. When your customers can make or break you, treating them well seems obvious.
However, there isn’t an excellent way within Salesforce to track Case Age in Business Days. You could easily create a Formula field that takes Closed Date – Created Date to get an understanding of the Case end-to-end, but that shows a number in total days. If your employees don’t work on weekends, then these calculations can be very far off. When a support employee opens a case on Friday afternoon and closes it Monday morning, this inaccurate formula would display a case time of 3 days!
To fix this, we want to take weekends out of each calculation. The formula below works perfectly for this.*
*Please note that both Closed_Date__c and Opened_Date__c are custom formula fields we created to gather only the date they were opened and closed. So in total, this solution uses 3 fields.
Closed_Date__c = DATEVALUE(ClosedDate) …&… Opened_Date__c = DATEVALUE(CreatedDate)
Create your own ‘Case Age in Business Days’ formula field (Number), and try it out for yourself!
IF( IsClosed,
CASE(MOD( Opened_Date__c – DATE(1985,6,24),7),
0 , CASE( MOD(Closed_Date__c – Opened_Date__c,7),1,2,2,3,3,4,4,5,5,5,6,5,1),
1 , CASE( MOD(Closed_Date__c – Opened_Date__c,7),1,2,2,3,3,4,4,4,5,4,6,5,1),
2 , CASE( MOD(Closed_Date__c – Opened_Date__c,7),1,2,2,3,3,3,4,3,5,4,6,5,1),
3 , CASE( MOD(Closed_Date__c – Opened_Date__c,7),1,2,2,2,3,2,4,3,5,4,6,5,1),
4 , CASE( MOD(Closed_Date__c – Opened_Date__c,7),1,1,2,1,3,2,4,3,5,4,6,5,1),
5 , CASE( MOD(Closed_Date__c – Opened_Date__c,7),1,0,2,1,3,2,4,3,5,4,6,5,0),
6 , CASE( MOD(Closed_Date__c – Opened_Date__c,7),1,1,2,2,3,3,4,4,5,5,6,5,0),
999)
+
(FLOOR((Closed_Date__c – Opened_Date__c)/7)*5-1),
CASE(MOD( Opened_Date__c – DATE(1985,6,24),7),
0 , CASE( MOD(TODAY() – Opened_Date__c,7),1,2,2,3,3,4,4,5,5,5,6,5,1),
1 , CASE( MOD(TODAY() – Opened_Date__c,7),1,2,2,3,3,4,4,4,5,4,6,5,1),
2 , CASE( MOD(TODAY() – Opened_Date__c,7),1,2,2,3,3,3,4,3,5,4,6,5,1),
3 , CASE( MOD(TODAY() – Opened_Date__c,7),1,2,2,2,3,2,4,3,5,4,6,5,1),
4 , CASE( MOD(TODAY() – Opened_Date__c,7),1,1,2,1,3,2,4,3,5,4,6,5,1),
5 , CASE( MOD(TODAY() – Opened_Date__c,7),1,0,2,1,3,2,4,3,5,4,6,5,0),
6 , CASE( MOD(TODAY() – Opened_Date__c,7),1,1,2,2,3,3,4,4,5,5,6,5,0),
999)
+
(FLOOR((TODAY() – Opened_Date__c)/7)*5-1)
)