Salesforce Date Formula Business Days

You’ve come to the right place if you want to 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 and Salesforce Classic to calculate business days between two dates.

While working for an organization, I was assigned a job to determine the number of business days. So, to fulfill this requirement, I define an advanced formula that calculates a certain number of business days from a specific date.

The following are the topics that we will cover in this salesforce tutorial:

• Salesforce Date Formula Business Day
• Calculate Business Day in Salesforce Lightning
• Calculate Business Day in Salesforce Classic

Salesforce Date Formula Business Day

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

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 the date of the upcoming campaign within three business days and subtract all the holidays if falls any in between.

Unfortunately, Salesforce doesn’t have a function for handling business days, so we’ll have to make your 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 called “days_later”. It returns the result in a date format that falls on business days i.e Monday to Friday.
• The function WEEKDAY() returns the day of the week as an integer, where 1 denotes Sunday, 2 denotes 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 occur within the “days_later” range for Monday through Friday i.e. cases 1 to 5 we multiplied that number by two before adding it to the initial date.
• We use the FLOOR function to round down the result to the nearest integer so that the number of weekends that fall within the range is counted effectively.
• The CEILING function is used to round numbers up to the nearest integer in case 6 and in case 7, the IF function is used to handle values for the “days_later” variable that are negative.

We now know how to Salesforce Date Formula Business Day. The next step is to learn how to use Salesforce Lightning to calculate the business day i.e working days.

Calculate Business Days in Salesforce Lightning

Here, are the steps to define a formula Calculate Business Day in Salesforce Lightning.

Step 1: Click the Setup icon in the top right corner of the page to access Salesforce Setup.

Step 2: Click Object Manager from the menu’s left side.

Step 3: Look for the item to which we want to calculate business days. I choose the Campaign object from the list of objects in this instance.

Step 4: Choose Fields & Relationships.

Step 5: Next, select New.

Step 6: Choose the Formula as the field type.

Step 7: Click Next to proceed.

Step 8: After entering a Field Label for the formula, the Field Name will be filled in automatically. Here, I enter Field Label Upcoming Campaign.

Step 9: Select Date as the Formula Return Type. After that, click Next.

Step 10: Enter the business day formula in the Advance Formula editor. Here, is the formula:

CASE(
  WEEKDAY(TODAY() + 4),
  1, (TODAY() + 4) + 1 + FLOOR(3/5)*2,
  2, (TODAY() + 4) + 1 + FLOOR(4/5)*2,
  3, (TODAY() + 4) + 1 + FLOOR(5/5)*2,
  4, (TODAY() + 4) + 1 + FLOOR(6/5)*2,
  5, (TODAY() + 4) + 1 + FLOOR(7/5)*2,
  6, (TODAY() + 4) + 1 + CEILING(4/5)*2,
  7, (TODAY() + 4) - IF(1>0,1,0) + 1 + CEILING(4/5)*2,
  null
)

• We want to set the date of the Upcoming Campaign after 4 days from today. So, for this, we need to check if today + 4 is a weekday or not. So, if it is not, we will set another date.
• Here we add 4 days to the current date using the TODAY() function and then uses the WEEKDAY() function to get the day of the week where 1 for Sunday, 2 for 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 of the function represents a different day of the week.
• Since we are just adding 4 days, the number of days is now fixed to 1. The FLOOR and CEILING functions have been reflected that the current date is only being advanced by one day.
• The formula used will produce a result that is 4 days including weekends. As today is April 18, 2023, Tuesday it adds 4 days to it and returns April 22, 2023, Saturday.
• Now, the formula will follow the logic of the 6th case and represents the next nearest business day after April 22, 2023, which is April 24, 2023, Monday.
• In conclusion, the formula indicates that April 22, 2023, is not a business day, it is a holiday.

Step 11: To verify that the formula is valid, click Check Syntax.

Step 12: We can specify how you want to handle the empty field and include the Description and Help Text as alternatives.

Step 13: Then, select Next.

Step 14: Select the appropriate field Level Security and to make it visible to everyone, here we select the Visible checkbox.

Step 15: Select Next.

Step 16: Select the Page Layout Name for which the formula field is to be added.

Step 17: Click Save to save the formula field.

We can use the formula field once we’ve created it. Let’s look at an example:

• Create a campaign by opening the Campaigns item and save it.
• After that, click on the Details section, and here the Upcoming Campaign Date field appears which shows the next campaign date after 4 days of today’s date but it should be a business day.

We now know how to use Salesforce Lightning’s advanced formula to Calculate Business Days. Next, we’ll look at how to use the advanced formula in Salesforce Classic to Calculate Business Day.

Read Salesforce Formula Date Greater Than Today

Calculate Business Days in Salesforce Classic

Here, are the steps to define a formula Calculate Business Day in Salesforce Classic.

Step 1: From the Salesforce Lightning to open your Salesforce Classic edition. Click on Avtar and click Switch to Salesforce Classic.

Step 2: Click on the Setup option.

Step 3: Scroll down to the Build section. Then under Customize, select the object for whom we want to calculate the business day. Here, I choose the Product from the object list.

Step 4: Click on the dropdown menu and select Fields.

Step 5: As you scroll down the page, click on the New button under Custom Fields & Relationships.

Step 6: Select the Formula as the field type.

Step 7: Click Next to proceed.

Step 8: Enter a Field Label for the formula, and the Field Name will be automatically populated. Here, I enter Field Label as Delivery Date.

Step 9: Choose Date as the Formula Return Type and then click Next.

Step 10: Enter the business day formula in the Advance Formula editor. Here, is the formula:

CASE(
WEEKDAY(Purchase_Date__c + 7),
1, (Purchase_Date__c + 7) +
FLOOR((7-1)/5)*2,
2, (Purchase_Date__c + 7) +
FLOOR((7)/5)*2,
3, (Purchase_Date__c + 7) +
FLOOR((7+1)/5)*2,
4, (Purchase_Date__c + 7) +
FLOOR((7+2)/5)*2,
5, (Purchase_Date__c + 7) +
FLOOR((7+3)/5)*2,
6, (Purchase_Date__c + 7) +
CEILING((7)/5)*2,
7, (Purchase_Date__c + 7) - IF(7>0,1,0) +
CEILING((7)/5)*2,
null)

• This formula is used to calculate the delivery date based on the purchase date plus 7 days.
• We utilize the WEEKDAY function to determine the day of the week for the date value of Purchase_Date__c + 7 days.
• The formula then applies a different formula dependent on the day of the week using a CASE function to evaluate the day of the week returned by the WEEKDAY function.
• For days 1-6, the formula calculates the delivery date by adding the number of days to the purchase date and adding the number of weekends in between.
• For day 7, the formula determines the delivery date by deducting one day from the purchase date, adding the total number of days, and then including the total number of weekends.
• We calculated the number of weekends by dividing the number of days by 5 and rounding down to the nearest integer using the FLOOR function on days 1-5 and 7 also. But for day 6, we use the CELLING function for calculation.
• If the WEEKDAY function returns null, the CASE method also returns null.

Step 11: To verify that the formula is valid, click Check Syntax.

Step 12: We can specify how you want to handle the empty field and include the Description and Help Text optionally.

Step 13: Then, click Next.

Step 14: Choose the appropriate Field’s Level Security, and then check the box Visible to make it visible to all.

Step 15: Click Next.

Step 16: Choose the Page Layout Name for which the formula field is to be added.

Step 17: Click Save to save the formula field.

Once we’ve defined the formula field, we may use it. Let’s explore an example:

• Create a product with the Purchase Date field value by opening the Products tab and saving it.
• In the details section, we will get the Delivery Date field which shows the delivery date after 7 days of the purchase date. It should only include business days.

In a conclusion, we now understand the advance formula to calculate business day in Salesforce Classic.

Conclusion

In conclusion, we learned the advanced salesforce formula to calculate business day.

Moreover, we have learned that both Salesforce Classic and Salesforce Lightning provide a simple procedure for determining the number of business days.

In addition to this, the following are the topics that we have discussed:

• Salesforce Date Formula Business Day
• Calculate Business Day in Salesforce Lightning
• Calculate Business Day in Salesforce Classic

You may also like the following Salesforce tutorials:

• Salesforce Formula Date Less Than Today
• Salesforce Formula Date Greater Than Specific Date
• Difference between Two Datetime Fields in Salesforce
• Remove comma from number field Salesforce