Monday, September 19, 2011

Calculate Case Age considering Salesforce Business Hours

Requirement being Salesforce Business Hours need to be considered while calculating a Case Age, and Business hours can change anytime:
Case 1: The Default Business Hours are Mon -9 am to 5 pm, tue -9 am to 5 pm, wed -9 am to 5 pm, thu -9 am to 5 pm, fri -9 am to 5 pm.
A case opened on Monday 10 am when checked on the following tuesday 9 am should show 8 hours
Case 2: The Default Business Hours are Mon -10 am to 6 pm, tue -8 am to 4 pm, wed -9 am to 6 pm, thu -9 am to 5 pm, fri -8 am to 4 pm
A case opened on Monday 10 am when checked on the following tuesday 9 am should show 9 hours

Also, if national holidays or public holidays [which might occur on weekdays] need to be excluded while calculating the Case - A scheduled apex is the only way. The following code snippet gives a solution to Case Age considering default Salesforce Business Hours.

global class ScheduledApexOnCase implements Schedulable {

global void execute(SchedulableContext sc) {

Date today = system.today();
Decimal caseAge;
List newCaseList = new List();

BusinessHours stdBusinessHours = [select id from businesshours where isDefault = true];
newCaseList = [Select id, CreatedDate, Status, CaseAge__c from Case]; //CaseAge__c is a Text field
for(Case eachCase: newCaseList) {
if(eachCase.Status != 'Closed')
caseAge = BusinessHours.diff(stdBusinessHours.id, eachCase.CreatedDate, today) / 1000 / 3600;
else
caseAge = BusinessHours.diff(stdBusinessHours.id, eachCase.CreatedDate, eachCase.ClosedDate) / 1000 / 3600;
eachCase.CaseAge__c = String.valueOf(caseAge) + ' hours';
}
update newCaseList;
}
}


Note:
Business Hours can be modified on Admin SetUp | Company Profile | Business Hours
BusinessHours.diff(stdBusinessHours.id, eachCase.CreatedDate, today) / 1000 / 3600; // This can be further divided by 8 or 9 hours to get the number of days [considering :working hours of 8 or 9 per day]
The ScheduledApex should run once in an hour to show the case age in number of hours