Business days can serve as essential data points for a range of essential operations. Use cases run the gamut from service agent requests to employee utilization. Salesforce provides some base functionality in regards to these calculations, but expanding it to fit non-native situations will likely require more advanced configurations.
There are a number of implementation guides floating around online that try to deal with these calculations via formula fields, but they can be confusing and generally cannot account for any flexibility. This flexibility is important because every company has its own set of dates throughout the calendar that may need to be omitted when accounting for business days in Salesforce. These days may include:
The most common calculation solutions you’ll read about are those that reference a known starting date. This is generally the first Sunday in the year 1900, but you’re free to choose and work around any arbitrary date depending on what it is you’re looking to accomplish. Once that reference date is selected, you’ll need to calculate the number of days between any other two dates with the reference date being used to determine the day of the week. After that, you’ll divide the difference by seven to get the total number of weeks. You can then infer that within any seven day period, there are five working business days.
Need to merge multiple Salesforce orgs? Here are four critical steps your company needs to take.
As mentioned, holidays are one of the most common exceptions you’re likely to experience when calculating business days. If your business observes a holiday that falls within the selected timeframe, then your calculation will be off by a day. Taking this a step further, if two holidays are close together—let’s use Christmas and the New Year as an example—they can throw off your calculation even more.
Since the average American business grants upwards of ten federal holidays each fiscal year, the formula needed to incorporate multiple holidays will be unavoidably complex. So complex, in fact, that the time and labor required will render the whole practice pointless.
When working with Apex, the formula works the same as previously mentioned. You’ll take the difference of the days, divide by seven to get the number of weeks and multiply by five. You’ll then deal with the leftover days.
To reconcile these remaining days, you need to figure out whether or not these leftover days are business days or not. The formula relies on knowing a fixed date in the past, but it’s not necessary in Apex to have a fixed starting date. Having said that, one can be used to simplify the calculations. If you want to calculate dates on the fly, an algorithm such as Zeller’s congruence can be used.
Pro Tip: There’s an explicit check to see if the difference between the two days is zero, which would mean that they’re the same date. In some situations you may want to start with a value of 1 instead of 0, so you can adjust the calculation accordingly.
By iterating over the remaining days (remainder) in our calculation, we can check each day to determine if it should count towards your intended result or not. In other words, is it a business day? By taking our first calculation (number of weeks multiplied by five) and then adding these remaining values, we achieve the total number of business days!
Well… not quite. We haven’t accounted for any holidays (or any other exceptions that are also affecting our calculations.) For holidays, it’s best to store them in Salesforce as a record that can be referenced. At a minimum, this should include a name and a date, but other field values such as a region or location can be useful. For all of the calculations we run, we know what the start and end dates are, and therefore, we know the dates we need to check for holidays.
Once we have a total for the number of holidays, we can subtract that from the total business days calculated to get the actual number of business days.
Dates calculations are straightforward enough that if somebody gave you two dates, you could figure out the answers to that limited range, but engineering a formula that can serve as a sustainable solution is trickier. Nevertheless, these exceptions must be accounted for by any company attempting to meaningfully leverage business days as data points. And while we only have a handful of holidays here in the United States that are generally observed by all, the set of dates can be much more substantial in other countries.
If your organization has offices around the world, you likely have already run into this situation. Geographical exceptions can make handling this through a formula even more complicated. And let’s not get started on timezones. That doozy of a business challenge will require an actual Canpango consultation.
Christopher Knitter is a thirteen-time certified Salesforce developer at Canpango. Having spent a decade building Salesforce environments for clients of all sizes and industry types, Christopher is a go-to resource for grooming and mentoring the next wave of Salesforce architects. Through his experience in designing beautifully branded and easy-to-navigate interfaces, Christopher puts the Salesforce user first—whether that be an end-customer, a sales partner or an internal employee.
Stay in the know with insights and updates delivered directly to your inbox.