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 occur within the “days_later” range for Monday through Friday, i.e., cases 1 to 5, we multiplied 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 cases 6 and 7, the IF function is used to handle values for the “days_later” variable that are negative.
We now know how to use the Salesforce Date Formula for 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 to calculate Business Day in Salesforce Lightning.
- Click the Setup icon in the top right corner of the page to access Salesforce Setup.
- Click Object Manager from the menu’s left side.
- Look for the item to which we want to calculate business days. I chose the Campaign object from the list of objects in this instance.
- Choose Fields & Relationships. Next, select New.
- Select “Formula” as the field type. Click Next to proceed.
- After entering a Field Label for the formula, the Field Name will be filled in automatically. Here, I enter the Field Label for the Upcoming Campaign. Select Date as the Formula Return Type. After that, click Next.
- Enter the business day formula in the Advanced 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 for four days from today. Therefore, 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 use the WEEKDAY() function to get the day of the week, where 1 is Sunday, 2 is 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 adjusted so that the current date is only being advanced by one day.
- The formula used will produce a result of 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 represent 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.
- To verify that the formula is valid, click Check Syntax.
- We can specify how you want to handle the empty field and include the Description and Help Text as alternatives.
- Then, select Next.
- Select the appropriate Field Level Security, and to make it visible to everyone, we select the Visible checkbox. Select Next.
- Select the Page Layout Name for which the formula field is to be added. Click Save to save the formula field.
- We can use the formula field once it has been created. Let’s look at an example:
- Create a campaign by opening the Campaigns item and saving it.
- After that, click on the Details section, and the Upcoming Campaign Date field appears, showing the next campaign date, which is 4 days after today’s date, provided it is 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 days.
Conclusion
In conclusion, we learned the advanced Salesforce formula to calculate business days.
Moreover, we have learned that Salesforce Lightning provides a simple procedure for determining the number of business days.
You may also like the following Salesforce tutorials:
- Salesforce Formula Date Less Than Today
- Use Date Formula in Salesforce Report
- Salesforce Formula Date Greater Than Specific Date
- Difference between Two Datetime Fields in Salesforce
- Remove a comma from the number field Salesforce
I am Bijay Kumar, the founder of SalesforceFAQs.com. Having over 10 years of experience working in salesforce technologies for clients across the world (Canada, Australia, United States, United Kingdom, New Zealand, etc.). I am a certified salesforce administrator and expert with experience in developing salesforce applications and projects. My goal is to make it easy for people to learn and use salesforce technologies by providing simple and easy-to-understand solutions. Check out the complete profile on About us.
