function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
vahidkhan lodhivahidkhan lodhi 

I want to calculate days between two dates excluding weekends and Holidays which i added in salesforce. So how can i do it ? If any one have any formula for this one,Please Send me the solution...Thanks.

NagendraNagendra (Salesforce Developers) 
Hi Lodhi,

Please refer to below.

Calculate the number of days between two dates while excluding weekends or weekdays.
Formulas provided:

1. Weekday Count Formula
2. Weekend Days Count Formula
Steps to create:
1. Create a formula field that returns a number.
2. Paste in one of the two formulas.
3. Replace StartDate__c and EndDate__c with your custom field values.
4. If using Date/Time fields:
Replace with DATEVALUE(YourCustomDateTime__c) instead.

Notes:
Weekdays are defined as Mon-Fri and weekends as Sat & Sun
Holidays are not addressed

June 24, 1985 is a long distant Monday used as a reference point
The result will include BOTH the START and END dates.
Mon-Sun is counted as 5 Weekdays and 2 Weekend Days.

Mon-Fri is NOT (Fri subtract Mon) = 4 Elapsed Days.
Sat-Sun is NOT (Sun subtract Sat) = 1 Elapsed Day.
If you use another Formula field as the start or end dates you MAY hit a compilation limit.

Workaround - Use workflow rules to save the output of the formula fields into a regular date field.

REMINDER: Be sure to TEST the formulas FIRST.
Weekday Count Formula:
CASE(MOD( StartDate__c - DATE(1985,6,24),7), 
  0 , CASE( MOD( EndDate__c - StartDate__c ,7),1,2,2,3,3,4,4,5,5,5,6,5,1), 
  1 , CASE( MOD( EndDate__c - StartDate__c ,7),1,2,2,3,3,4,4,4,5,4,6,5,1), 
  2 , CASE( MOD( EndDate__c - StartDate__c ,7),1,2,2,3,3,3,4,3,5,4,6,5,1), 
  3 , CASE( MOD( EndDate__c - StartDate__c ,7),1,2,2,2,3,2,4,3,5,4,6,5,1), 
  4 , CASE( MOD( EndDate__c - StartDate__c ,7),1,1,2,1,3,2,4,3,5,4,6,5,1), 
  5 , CASE( MOD( EndDate__c - StartDate__c ,7),1,0,2,1,3,2,4,3,5,4,6,5,0), 
  6 , CASE( MOD( EndDate__c - StartDate__c ,7),1,1,2,2,3,3,4,4,5,5,6,5,0), 
  999) 
  + 
  (FLOOR(( EndDate__c - StartDate__c )/7)*5) 
Weekend Days Count Formula:

CASE(MOD( StartDate__c - DATE(1985,6,24),7),
0 , CASE( MOD( EndDate__c - StartDate__c, 7),1,0,2,0,3,0,4,0,5,1,6,2,0),
1 , CASE( MOD( EndDate__c - StartDate__c, 7),0,0,1,0,2,0,3,0,4,0,5,2,2),
2 , CASE( MOD( EndDate__c - StartDate__c, 7),0,0,1,0,2,0,3,1,2),
3 , CASE( MOD( EndDate__c - StartDate__c, 7),0,0,1,0,2,1,2),
4 , CASE( MOD( EndDate__c - StartDate__c, 7),0,0,1,1,2),
5 , CASE( MOD( EndDate__c - StartDate__c, 7),0,1,2),
6 , CASE( MOD( EndDate__c - StartDate__c, 7),6,2,1),
999)
+
(FLOOR(( EndDate__c - StartDate__c )/7)*2)
Hope this helps.

Kindly mark this as solved if the reply was helpful.

Thanks,
Nagendra

 
vahidkhan lodhivahidkhan lodhi
Thanks for your reply Nagendra ,But actually I want to exclude Holidays as well ..so if you have any other solution for this please revert back.
Amad Khan 10Amad Khan 10
Did you ever find a way to exlude Holidays??
Daniela Covi 2Daniela Covi 2
I found this blog which solved the problem of excluding holidays from the formula via the creation of a flow+process builder combo - it worked great for me :) 

https://munawirrahman.medium.com/count-duration-exclude-weekends-and-public-holidays-in-salesforce-without-any-codes-285277df35d