[SalesForce] Using formula to exclude weekend and holidays in salesforce

I have a requirement where in, i have to exclude public holidays listed below:
(14-Jan-2015, 26-Jan-2015, 06-Mar-2015, 17-Sep-2015, 02-Oct-2015, 22-Oct-2015, 11-Nov-2015, 12-Nov-2015, 13-Nov-2015, 25-Dec-2015) plus weekends to calculate number of days between start date and end date.

I have created formula field which excludes weekends but i need help to exclude holidays mention above using below formula field.

CASE(MOD( LM_Start_Date__c - DATE(1985,6,24),7), 
0 , CASE( MOD( LM_End_Date__c - LM_Start_Date__c ,7),1,2,2,3,3,4,4,5,5,5,6,5,1), 
1 , CASE( MOD( LM_End_Date__c - LM_Start_Date__c ,7),1,2,2,3,3,4,4,4,5,4,6,5,1), 
2 , CASE( MOD( LM_End_Date__c - LM_Start_Date__c ,7),1,2,2,3,3,3,4,3,5,4,6,5,1), 
3 , CASE( MOD( LM_End_Date__c - LM_Start_Date__c ,7),1,2,2,2,3,2,4,3,5,4,6,5,1), 
4 , CASE( MOD( LM_End_Date__c - LM_Start_Date__c ,7),1,1,2,1,3,2,4,3,5,4,6,5,1), 
5 , CASE( MOD( LM_End_Date__c - LM_Start_Date__c ,7),1,0,2,1,3,2,4,3,5,4,6,5,0), 
6 , CASE( MOD( LM_End_Date__c - LM_Start_Date__c ,7),1,1,2,2,3,3,4,4,5,5,6,5,0), 
999) 
+ 
(FLOOR(( LM_End_Date__c - LM_Start_Date__c )/7)*5)

Appreciate suggestions.

Best Answer

For a more robust solution, you should use the built in <Holiday> object.

This lets the Sys Admin set up any holidays, so you don't need to push updates for the new year.

It looks like the Holiday object isn't accessable for formula fields or validation rules, so you'll have to write it in a trigger.

If you want to calculate working days between two dates then you would have to iterate over every day in the range, and then iterate over every holiday rule and see if it applies.

To reduce the burden you might want to split out the non recurring ones, since if you know its not in a date range then you don't need to recheck.

Related Topic