Formulas for adding Business Days

on
Business Days
Imagine that you send a quote to a Contact and you want to set a Task to remind you to follow up in 2 days. But what if you sent the quote on Thursday? Thursday + 2 days is Saturday, and your Contact won't be in the office then. So really, you want to send it in 2 business days. Unfortunately, there's no business days function in Salesforce, so you have to create a formula to handle that.

If you search online, you'll find any number of sources to provide the formula, with varying degrees of clarity on the explanation. A recent search shows that almost all of them are using an old approach to finding the day of the week that was in common usage before Salesforce existed. It uses the MOD() function from math, and depends on you knowing that January 7, 1900 was a Sunday.

If you take any date, your birthday for example, and subtract the date for January 7, 1900, you get X days. Divide X by 7 and ignore that result, but pay attention to the remainder. Since you divided by 7, you won't have a remainder larger than 6. You can determine the day of the week for your birthday:

  • 0 = Sunday
  • 1 = Monday
  • 2 = Tuesday
  • 3 = Wednesday
  • 4 = Thursday
  • 5 = Friday
  • 6 = Saturday
Most of the formulas that I've seen referenced, including in Salesforce's own docs, end up as some variation of this one, which adds 3 business days. If you want to add some other number of days, you have to understand the formula well enough to add or delete lines.
CASE( 
  MOD( date - DATE( 1900, 1, 7 ), 7 ),
  3, date + 2 + 3,
  4, date + 2 + 3,
  5, date + 2 + 3,
  6, date + 1 + 3,
  date + 3
)
I found this other formula in a few places, which leaves the number of days to add as a variable in the formula. In this case, we use the FLOOR() and CEILING() functions to dynamically determine whether weekend days are involved. 
CASE(
 MOD(StartDate__c - DATE(1900, 1, 7), 7),
  0, (StartDate__c) + NumberOfDays__c + FLOOR((NumberOfDays__c-1)/5)*2,
  1, (StartDate__c) + NumberOfDays__c + FLOOR((NumberOfDays__c)/5)*2,
  2, (StartDate__c) + NumberOfDays__c + FLOOR((NumberOfDays__c+1)/5)*2,
  3, (StartDate__c) + NumberOfDays__c + FLOOR((NumberOfDays__c+2)/5)*2,
  4, (StartDate__c) + NumberOfDays__c + FLOOR((NumberOfDays__c+3)/5)*2,
  5, (StartDate__c) + NumberOfDays__c + CEILING((NumberOfDays__c)/5)*2,
  6, (StartDate__c) - IF(NumberOfDays__c>0,1,0) + NumberOfDays__c + CEILING((NumberOfDays__c)/5)*2,
null)

I was really impressed to see another approach from Lightning Champion John Knight on the Cloud Trailz blog. John takes advantage of a built-in function called WEEKDAY() that returns the day of the week.


If you've been using the MOD() function, you're probably used to thinking of Sunday = 0, but this one starts Sunday = 1, so don't let that trip you up. Using this function, look how simple John's formulas are:

Add 2 Weekdays:

date_field__c + 
 CASE( 
  WEEKDAY(date_field__c), 
   1, 2,
   2, 2,
   3, 2,
   4, 2,
   5, 4,
   6, 4,
   7, 3,
 0)
Short and clean! Check out John's full post for other examples. If you want to add the flexibility for any number of days, while using the WEEKDAY() function, it looks like this:
CASE(
 WEEKDAY(date_field__c),
  1, (date_field__c) + number_of_days__c + 
      FLOOR((number_of_days__c-1)/5)*2,
  2, (date_field__c) + number_of_days__c + 
      FLOOR((number_of_days__c)/5)*2,
  3, (date_field__c) + number_of_days__c + 
      FLOOR((number_of_days__c+1)/5)*2,
  4, (date_field__c) + number_of_days__c + 
      FLOOR((number_of_days__c+2)/5)*2,
  5, (date_field__c) + number_of_days__c + 
      FLOOR((number_of_days__c+3)/5)*2,
  6, (date_field__c) + number_of_days__c + 
      CEILING((number_of_days__c)/5)*2,
  7, (date_field__c) - IF(number_of_days__c>0,1,0) + number_of_days__c + 
      CEILING((number_of_days__c)/5)*2,
 null)

I think that one gets my vote for cleanest and most flexible. Now, we just need somebody to figure out how to handle holidays!

UPDATE: Somebody figured out how to handle holidays!

Less than a day after my post, Jason Hoult pointed me to a blog by Cheney Shreve. In her remarkable post Traversing the declarative and programmatic divide: The same task with 3 different Salesforce Tools, Cheney shows how to use the Business Hours setting, in combination with Workflow Rules, Processes, and Invocable Apex, to create Holiday settings and add DateTime into the equation. I recommend reading it.e

Comments

Post a Comment