Count Duration Exclude Weekends and Public Holidays in Salesforce Without Any Codes

Currently, there is no default way for Salesforce to count Duration (days)excluding Weekends and Public Holidays in a field.

The Idea for this feature has been posted since 2012 in https://success.salesforce.com/ideaView?id=08730000000I75dAAC

Based on my google search research, currently there is a way to achieve this function using Apex Trigger as you can see here. But this can only configured if we have knowledge about Apex Trigger and not ideal way for an Admin.

In this article, I would like to share how I achieve this without Apex at all and only using Opportunity Object and default Holidays Configuration Object. The use case for this article is I want to count Opportunity Duration from Created Date to Close Date.

a. Add your local holidays in Setup (Setup -> Search for Holidays in Quick Search), Flag as recurring if the holiday occurs annually

b. Create a new custom number field In Opportunity for Holidays counter between the Created Date and Close Date I named this field Holidays Counter

c. Create an Autolaunched Flow (Setup-> Search Flow->New Flow)to access the Holidays Object and count the holiday days between Created Date and Close Date. You can follow the configuration from images below

Create Input Variable for Start Date (Created date will be passed to this variable)

Create Input Variable for End Date (Close date will be passed to this variable)

Create new Get Elements to Get Holiday Object Filter using Start date and Date

Store all records and all fields

Create new Variable to store the number of holidays you got from the get records

Create new Assignment Element to count how many Holidays you have between Start Date and End Date and store it in the the number variable you create earlier

Create new input text variable to store OpportunityId and as a filter when update the Holidays_Counter__c field in Opportunity

Create new Update Element filter based on recordId and update Holidays_Counter__c field with value from holidays_counter

Your Flow will look like this. Try to debug the flow using OpportunityId, StartDate, and EndDate and activate it

d. Create a process builder as a a trigger in Opportunity Object (Setup-> Search Process Builder->New) You can follow the configuration from images below

Create a new process builder and set the process starts when A record changes

Select Opportunity as the Object, Start the process when a record is created or edited and allow Recursion

I set no conditions, so anytime the opportunity edited the counter will be updated as well

Add Immediate Action with Action Type = Flow , Flow = Name of the flow we create earlier, set the recordId Variable to Opportunity.Id, Start_date to CreatedDate, and End_date to CloseDate

Process Builder will look like this, save and Activate the process builder

e. Create a new number formula field to count the Duration excluding Weekends and Public Holidays

Set the formula field value to this, if your start date or end date is in Date/Time value, convert it using DATEVALUE() formula.

IF(
((CASE(MOD(CloseDate — DATE(1985,6,24),7),
0 , CASE( MOD(CloseDate — DATEVALUE(CreatedDate) ,7),1,2,2,3,3,4,4,5,5,5,6,5,1),
1 , CASE( MOD(CloseDate — DATEVALUE(CreatedDate) ,7),1,2,2,3,3,4,4,4,5,4,6,5,1),
2 , CASE( MOD(CloseDate — DATEVALUE(CreatedDate) ,7),1,2,2,3,3,3,4,3,5,4,6,5,1),
3 , CASE( MOD(CloseDate — DATEVALUE(CreatedDate) ,7),1,2,2,2,3,2,4,3,5,4,6,5,1),
4 , CASE( MOD(CloseDate — DATEVALUE(CreatedDate) ,7),1,1,2,1,3,2,4,3,5,4,6,5,1),
5 , CASE( MOD(CloseDate — DATEVALUE(CreatedDate) ,7),1,0,2,1,3,2,4,3,5,4,6,5,0),
6 , CASE( MOD(CloseDate — DATEVALUE(CreatedDate) ,7),1,1,2,2,3,3,4,4,5,5,6,5,0),
999)
+
(FLOOR((CloseDate — DATEVALUE(CreatedDate) )/7)*5))-1) = -1, 0,
((CASE(MOD(CloseDate — DATE(1985,6,24),7),
0 , CASE( MOD( CloseDate — DATEVALUE(CreatedDate) ,7),1,2,2,3,3,4,4,5,5,5,6,5,1),
1 , CASE( MOD( CloseDate — DATEVALUE(CreatedDate) ,7),1,2,2,3,3,4,4,4,5,4,6,5,1),
2 , CASE( MOD( CloseDate — DATEVALUE(CreatedDate) ,7),1,2,2,3,3,3,4,3,5,4,6,5,1),
3 , CASE( MOD( CloseDate — DATEVALUE(CreatedDate) ,7),1,2,2,2,3,2,4,3,5,4,6,5,1),
4 , CASE( MOD( CloseDate — DATEVALUE(CreatedDate) ,7),1,1,2,1,3,2,4,3,5,4,6,5,1),
5 , CASE( MOD( CloseDate — DATEVALUE(CreatedDate) ,7),1,0,2,1,3,2,4,3,5,4,6,5,0),
6 , CASE( MOD( CloseDate — DATEVALUE(CreatedDate) ,7),1,1,2,2,3,3,4,4,5,5,6,5,0),
999)
+
(FLOOR(( CloseDate — DATEVALUE(CreatedDate) )/7)*5))-1)
) — Holidays_Counter__c

Save the field

f. Check the result

Duration (Days) as a result

If you want to check the validity of the result, you can go to https://www.timeanddate.com/date/workdays.html and set it based on your country, start date, and end date.

Enjoy, and comment if you need anything else!

Thanks