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?


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;
| improve this answer | |
  • Currently I am excluding weekends but I have to exclude holidays as well – Ram Sep 11 '14 at 5:20
  • Can you Provide that formula for excluding holidays as well – Ram Sep 11 '14 at 5:28
  • Hi Atulrajguru can you provide the formula for excluding holidays and weekends – Ram Sep 11 '14 at 5:38
  • You can NOT excluding holidays in formula field. – AtulRajguru9 Sep 11 '14 at 6:04
  • 1
    @user10921, I've updated my answer to include the holidays – Cloud Ninja Sep 11 '14 at 6:40

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.