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
Phuc Nguyen 18Phuc Nguyen 18 

Calculate date difference with holiday and weekend

Hello All,
I have a formula to  calculate the number of business days excluding weekend and holidays,
The issue is is not very user friendly.  Specifically the holiday part.  Here is the  holiday formula.
((IF(AND(Request_F__c <= DATEVALUE("2021-11-24"),TODAY() >= DATEVALUE("2021-11-24")),1,0) + IF(AND(Request_F__c <= DATEVALUE("2021-12-22"),TODAY() >= DATEVALUE("2021-12-22")),1,0) + IF(AND(Request_F__c <= DATEVALUE("2021-01-01"),TODAY() >= DATEVALUE("2021-01-01")),1,0)))

Issue is what if the holiday is on a weekend?  The holiday date will be included so the date diff will be incorrect. And the formula has to be updated every year.
I thought about apex but it will be the same issue plus the code only runs when the record is updated so the difference will not be calculated when the user opens the record page.
Any suggestions?  Would love to have a solution using the SF holiday(recurring) and business days functionality.
Thank you,
Best Answer chosen by Phuc Nguyen 18
PriyaPriya (Salesforce Developers) 
Hi Phuc,

You can try this formula to get exact number of business days excluding holdiays :- 
((5 * ( FLOOR( (  Date_Completed__c  - DATE( 1900, 1, 8) ) / 7 ) ) + MIN( 5, MOD( Date_Started__c  - DATE( 1900, 1, 8), 7 ) ) )
-
(5 * ( FLOOR( ( Date_Started__c  - DATE( 1900, 1, 8) ) / 7 ) ) + MIN( 5, MOD( Date_Started__c  - DATE( 1900, 1, 8), 7 ) ) ) + 1 
-
IF(AND(Date_Started__c <= DATEVALUE("2016-11-24"),Date_Completed__c >= DATEVALUE("2016-11-24")),1,0) -
IF(AND(Date_Started__c <= DATEVALUE("2016-12-23"),Date_Completed__c >= DATEVALUE("2016-12-23")),1,0) -
IF(AND(Date_Started__c <= DATEVALUE("2016-12-26"),Date_Completed__c >= DATEVALUE("2016-12-26")),1,0) -
IF(AND(Date_Started__c <= DATEVALUE("2017-01-23"),Date_Completed__c >= DATEVALUE("2017-01-01")),1,0)

You can also refer to below knowledge article.

https://help.salesforce.com/articleView?id=000004526&type=1

However, you can use this Apex code to exclude holidays and weekends in Formula.

https://sfdcdev.wordpress.com/2011/09/24/handling-holidays-in-salesforce-apex/

Also, please refer to below link which might help you further.

https://success.salesforce.com/answers?id=9063A0000019QHUQA2


I hope it helps you.

Kindly let me know if it helps you and close your query by marking it as solved so that it can help others in the future.

Thanks and Regards,
Priya Ranjan