You need to sign in to do that
Don't have an account?
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.
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,
P
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,
P
You can try this formula to get exact number of business days excluding holdiays :-
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