Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
ShowAll Questionssorted byDate Posted
Phuc 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
Priya (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/