Using Apex BusinessHours Class Methods and Custom Setting to Calculate Resolution Date

One common requirement of calculating when a business event like a Case resolution, Task due date, or Opportunity Stage advancement should occur is to exclude weekends and holidays. Things become even more complicated if different types of records for a given object are due at different times (for example: Case type A gets two weeks to resolve but Case type B gets one month to resolve).

If the requirement was just to exclude weekends, a simple formula and some custom fields can handle it. However, even though a Saleforce org Holiday object is associated with the Business Hours class in the System namespace, it is not accessible in formulas. That’s right: you can’t use a formula to see how many holidays lay between now and then (whenever now and then are). You can’t even see if today is a holiday via formula.

The only thing to do is learn to love the BusinessHours Static Methods, and write triggers that will update your records accordingly.

The methods at first appear sparse, but they can be used in combination to become quite powerful (and avoid having to call out to a remote class with a more robust date library). It will require some understanding of Unix time stamps, but then you’ll be rolling.

The logic below does just such a combination, and perhaps it can save someone else an enormous headache. It is meant to calculate the date by which a case should be resolved (but it could be used to indicate when any type of record is due).

The requirement is:
1. Look at a Custom Setting to see what the associated number of “days until resolution due” is for a given case type (Example: (CS1 = Type A, Days 2) (CS2 = Type B, Days 3))
2. Use that number of days to create a value that is: Case created date + “days until resolution due as indicated in the custom setting” + weekends + holidays

c.Resolve_By__c = Date.valueOf(BusinessHours.add(c.BusinessHoursId, created, (((c.Days_Until_Resolution_Due_As_Indicated_In_Custom_Setting__c*BusinessHours.diff(c.BusinessHoursId, Datetime.newInstance(2013, 12, 18, 0, 0, 0), Datetime.newInstance(2013, 12, 18, 23, 59, 59))))).longValue()));

The code below works because our business was open 12/18/2013. It was a random Wednesday. I chose it because the code uses the BusinessHours.diff method to calculate (based on the BusinessHoursId reflected on the Business Hours field on the case), how long the office was open that day. Since all our office days are the same length of time, the method can return how many milliseconds we should multiply our c.Days_Until_Resolution_Due_As_Indicated_In_Custom_Setting__c by to get a parameter for BusinessHours.add (if you have open hours of various lengths, you’ll need to write a conditional statement to get the open length for each variation then see how many occur in your time frame). BusinessHours.add then gives us the created date + the days until resolution (skipping any day the office is closed).

This code works if a holiday is repeating or a one time event.

In case you were counting, we the number of SOQL queries required to get the days until resolution, holidays, and time open per day is 0, so if included in an update or insert trigger, the weight of this code should be quite small.

Posted in Learning