UPDATE: The original version of this post referenced counting business days. More precisely, the solution and formulas provided actually counts weekdays.
There are a number of scenarios where one might want to count weekdays 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 weekdays 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 leveraging a Salesforce formula to add days to date of the call, excluding weekends.
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
- The date you are starting with
- 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
- The CASE() function to add the correct number of days based on the day of the weekThis will be the number of weekdays 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 weekdays to a Sunday, we need only add three days. If we add three weekdays to a Friday, we’ll add 5 days – the three weekdays plus two weekend days. If we add three weekdays to a Saturday, we only need to add one weekend day to the three weekdays, for a total of 4 days added.When adding more than 5 weekdays, the function needs to factor in the possibility of multiple weekends. For example, adding 6 weekdays to a Friday will land on the Monday 10 days later.
Salesforce Formula Add Days to Date Common variations:
- Rather than adding number of weekdays to a date field, add it to TODAY()
- Example: schedule a followup call 3 weekdays from now
- Rather than adding number of weekdays, subtract number of weekdays
- Example: create a reminder task 7 days before opportunity close date
Copy and Past Formulas Skipping Weekends:
There is a handy function in Excel, WORKDAY() that can convert weekdays 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.