Salesforce Date Formula Business Days

Published on by

A company needs to calculate the delivery date of an order. The standard delivery time is 5 business days (excluding weekends).

If an order is placed on Wednesday, Salesforce should automatically calculate and show the delivery date as the following Wednesday (skipping Saturday and Sunday).

In this article, we learn about how to calculate business days in Salesforce. We will see Salesforce Date Formula Business Days. Additionally, we’ll cover how to use Salesforce Lightning to calculate business days between two dates.

Salesforce Date Formula Business Day

In Salesforce, we build an advanced formula field that determines whether a date falls on a business day or not. The formula compares a date field with the weekdays and checks whether the date falls on a working day or a holiday.

And, by default, we assume weekends as Saturday and Sunday in Salesforce. Let’s take a scenario and try to understand it.

Suppose, we want to decide the date of the upcoming campaign within the next three days. But what if the Toady is Thursday? Thursday + 3 days is Sunday, and our campaign members won’t be available on that date.

In simple terms, we should decide on the date of the upcoming campaign within three business days and subtract any holidays that fall in between.

Unfortunately, Salesforce doesn’t have a function for handling business days, so we’ll have to make our own formula. So, let’s create a formula and understand it.

CASE(
 WEEKDAY(datefield),
  1, (datefield) + days_later + 
      FLOOR((days_later-1)/5)*2,
  2, (datefield) + days_later + 
      FLOOR((days_later)/5)*2,
  3, (datefield) + days_later + 
      FLOOR((days_later+1)/5)*2,
  4, (datefield) + days_later + 
      FLOOR((days_later+2)/5)*2,
  5, (datefield) + days_later + 
      FLOOR((days_later+3)/5)*2,
  6, (datefield) + days_later + 
      CEILING((days_later)/5)*2,
  7, (datefield) - IF(days_later>0,1,0) + days_later + 
      CEILING((days_later)/5)*2,
 null)
  • In this formula, we determine the new date based on a given date, called “datefield”, and the number of days specified by “days_later”. It returns the result in a date format that falls on business days, i.e, Monday to Friday.
  • The WEEKDAY() function returns the day of the week as an integer, where 1 represents Sunday, 2 represents Monday, and so on.
  • Then, the CASE function is used to check the day of the week and decide how many working days should be added to the date.
  • And each line in the CASE function represents a different day of the week.
  • The number of weekends that o