Skip to main content
deleted 5 characters in body
Source Link

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 includeexclude holidays mention above as well inusing 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.

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 include holidays mention above as well in 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.

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.

Source Link

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 include holidays mention above as well in 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.