Formula for calculating case age excluding weekends and holidays

Question

I need to create a formula field or apex method for calculating case age (from CreatedDate to now) excluding weekends and holidays (as defined in the case's business hours).

Is it possible with a formula field? If not, how difficult is this to do in apex?

Answer 1

Probably not formula field that's for sure. I think this post might inspire your a lot, you probably just need to add the logic to exclude the holidays as well.

Update :

If I'm assuming you already have the number of days excluding the weekend.

Integer days = daysBetweenExcludingWeekends(caseRecord.CreatedDate,Datetime.now());
Integer holidays = getHolidays(caseRecord.CreatedDate,Datetime.now());
days -= holidays;

System.debug('Days excluding weekends and holidays : '+days);


public static Integer getHolidays(Date startDate, Date endDate){
    List<Holiday> holidays = [  Select Id, h.ActivityDate From Holiday h Where h.ActivityDate >= :startDate And h.ActivityDate <= :endDate];
    if (holidays != null)
        return holidays.size();
    return 0;
}

Answer 2

I don't see any way this would be possible in a formula. You can look into using the native entitlements and milestones features to do this declaratively if it's part of some sort of agreement with the customer you want to make sure is hit.

However if you decide that isn't a good fit there is an apex class, BusinessHours, which has methods for determining the amount of working time between two dates. Since the BusinessHours SObject is what contains any work schedules and holidays that have been defined this seems to be the most efficient way forward.