Count Business Days in Salesforce Formula

Count Business Days in Salesforce Formula

Business Scenario

There are a number of scenarios where one might want to count business days in a Salesforce formula.

Scenario One: Automated Follow-up Tasks

In this scenario, the business is tracking the sentiment of a phone call using a picklist field on the tasks. The available options are “Interested”, “Maybe Later”, “Not Interested”, and “Not a Good Time”. When the sentiment is “Maybe Later”, the business would like a followup task automatically generated for 20 business days later. When the sentiment is “Not a Good Time”, the business would like a followup task automatically generated for the next business day. The Salesforce admin uses process builder with criteria based on the sentiment to create a new task with the correct due date using a formula below.

Scenario Two: Calculating a shipping date

I need to be able to add product lead times (in business days) to an opportunity expected close date to determine the product shipping date. I have seen formulas for calculating number of business days between two dates, but I need the opposite. Does anyone have something they can share with me so I don't have to try to reverse engineer the between 2 dates formula? Thanks!

~ Jennifer Gabriel, On the Salesforce Customer Success Community

Formula Core Components

  1. The date you are starting with

  2. The WEEKDAY() function to determine which day of the week the date is

    • Sunday = 1

    • Monday = 2

    • Tuesday = 3

    • Wednesday = 4

    • Thursday = 5

    • Friday = 7

    • Saturday = 7

  3. The CASE() function to add the correct number of days based on the day of the week

    This will be the number of business days you are adding plus the number of weekend days that should be added based on the starting date.

    For example, if we want to add 3 business days to a Sunday, we need only add three days. If we add three business days to a Friday, we’ll add 5 days - the three business days plus two weekend days. If we add three business days to a Saturday, we only need to add one weekend day to the three business days, for a total of 4 days added.

    When adding more than 5 business days, the function needs to factor in the possibility of multiple weekends. For example, adding 6 business days to a Friday will land on the Monday 10 days later.

Copy and Past Formulas Skipping Weekends:

There is a handy function in Excel, WORKDAY() that can convert business days to calendar days. Until this function exists natively in Salesforce, we can use Excel to run the calculations and copy the formulas into Salesforce.

I generated these using a matrix of formulas in Excel to save time. Now they are copy-paste-edit ready for convenience.

Common edits:

  • Rather than adding number of business days to a date field, add it to TODAY()

    • Example: schedule a followup call 3 business days from now

  • Rather than adding number of business days, subtract number of business days

    • Example: create a reminder task 7 days before opportunity close date



Add 1 Business Day:

date_field__c + 
CASE( 
WEEKDAY(date_field__c), 
1, 1,
2, 1,
3, 1,
4, 1,
5, 1,
6, 3,
7, 2,
0)

Add 2 Business Days:

date_field__c + 
CASE( 
WEEKDAY(date_field__c), 
1, 2,
2, 2,
3, 2,
4, 2,
5, 4,
6, 4,
7, 3,
0)

Add 3 Business Days:

date_field__c + 
CASE( 
WEEKDAY(date_field__c), 
1, 3,
2, 3,
3, 3,
4, 5,
5, 5,
6, 5,
7, 4,
0)

Add 4 Business Days:

date_field__c + 
CASE( 
WEEKDAY(date_field__c), 
1, 4,
2, 4,
3, 6,
4, 6,
5, 6,
6, 6,
7, 5,
0)

Add 5 Business Days:

date_field__c + 
CASE( 
WEEKDAY(date_field__c), 
1, 5,
2, 7,
3, 7,
4, 7,
5, 7,
6, 7,
7, 6,
0)

Add 6 Business Days:

date_field__c + 
CASE( 
WEEKDAY(date_field__c), 
1, 8,
2, 8,
3, 8,
4, 8,
5, 8,
6, 10,
7, 9,
0)

Add 7 Business Days:

date_field__c + 
CASE( 
WEEKDAY(date_field__c), 
1, 9,
2, 9,
3, 9,
4, 9,
5, 11,
6, 11,
7, 10,
0)

Add 8 Business Days:

date_field__c + 
CASE( 
WEEKDAY(date_field__c), 
1, 10,
2, 10,
3, 10,
4, 12,
5, 12,
6, 12,
7, 11,
0)

Add 9 Business Days:

date_field__c + 
CASE( 
WEEKDAY(date_field__c), 
1, 11,
2, 11,
3, 13,
4, 13,
5, 13,
6, 13,
7, 12,
0)

Add 10 Business Days:

date_field__c + 
CASE( 
WEEKDAY(date_field__c), 
1, 12,
2, 14,
3, 14,
4, 14,
5, 14,
6, 14,
7, 13,
0)

Add 15 Business Days:

date_field__c + 
CASE( 
WEEKDAY(date_field__c), 
1, 19,
2, 21,
3, 21,
4, 21,
5, 21,
6, 21,
7, 20,
0)

Add 20 Business Days:

date_field__c + 
CASE( 
WEEKDAY(date_field__c), 
1, 26,
2, 28,
3, 28,
4, 28,
5, 28,
6, 28,
7, 27,
0)
Salesforce Formula: Count Business Days Graphic
Salesforce Trailblazer: Ben Fuller

Salesforce Trailblazer: Ben Fuller

Salesforce Trailblazer: John Knight

Salesforce Trailblazer: John Knight