2

I'm trying to come up with a way to exclude specific dates (holidays) from the following formula:

IF( IsClosed, 
CASE(MOD( Opened_Date__c - DATE(1985,6,24),7), 
0 , CASE( MOD(Closed_Date__c - Opened_Date__c,7),1,2,2,3,3,4,4,5,5,5,6,5,1), 
1 , CASE( MOD(Closed_Date__c - Opened_Date__c,7),1,2,2,3,3,4,4,4,5,4,6,5,1), 
2 , CASE( MOD(Closed_Date__c - Opened_Date__c,7),1,2,2,3,3,3,4,3,5,4,6,5,1), 
3 , CASE( MOD(Closed_Date__c - Opened_Date__c,7),1,2,2,2,3,2,4,3,5,4,6,5,1), 
4 , CASE( MOD(Closed_Date__c - Opened_Date__c,7),1,1,2,1,3,2,4,3,5,4,6,5,1), 
5 , CASE( MOD(Closed_Date__c - Opened_Date__c,7),1,0,2,1,3,2,4,3,5,4,6,5,0), 
6 , CASE( MOD(Closed_Date__c - Opened_Date__c,7),1,1,2,2,3,3,4,4,5,5,6,5,0), 
999) 
+ 
(FLOOR((Closed_Date__c - Opened_Date__c)/7)*5-1), 

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

There are 8 specific dates per year that I need to include for 5 years out, so 40 dates in total.

Is there a simple way to include these specific dates in the formula so that it will skip them when tallying the count?

Any ideas would be more than welcome, I'd love to do this without code!

  • Could you provide a bit more information? Specifically, what is the formula actually designed to do? – Christopher Alun Lewis Jan 25 '14 at 15:41
  • Hi Christopher, the formula needs to track Case Age in Business Days. So far we have it looking at M-F, but it still doesn't take the 8 holidays per year into account, which is what would be ideal. – McD Jan 27 '14 at 15:09
0

I had the same requirement and i did it with the following formula. It worked for me i hope it will be helpful:

End date and start dates - i am using as date range in which there will be holiday on 1 jan 2016 it will exclude weekends as well.

CASE(MOD(  start_date__c - DATE(1985,6,24),7), 
0 , CASE( MOD( end_date__c - start_date__c ,7),1,2,2,3,3,4,4,5,5,5,6,5,1), 
1 , CASE( MOD( end_date__c - start_date__c ,7),1,2,2,3,3,4,4,4,5,4,6,5,1), 
2 , CASE( MOD( end_date__c - start_date__c ,7),1,2,2,3,3,3,4,3,5,4,6,5,1), 
3 , CASE( MOD( end_date__c - start_date__c ,7),1,2,2,2,3,2,4,3,5,4,6,5,1), 
4 , CASE( MOD( end_date__c - start_date__c ,7),1,1,2,1,3,2,4,3,5,4,6,5,1), 
5 , CASE( MOD( end_date__c - start_date__c ,7),1,0,2,1,3,2,4,3,5,4,6,5,0), 
6 , CASE( MOD( end_date__c - start_date__c ,7),1,1,2,2,3,3,4,4,5,5,6,5,0), 
999) 
+ 
(FLOOR((  end_date__c - start_date__c )/7)*5)- (IF(AND( end_date__c  >  DATE(2016,01,1),
start_date__c < DATE(2016,01,1),
 CASE(MOD( DATE(2016,01,1) - DATE(1900, 1, 6), 7), 0, "Saturday", 1, "Sunday", 2,"Monday", 3, "Tuesday", 4, "Wednesday", 5, "Thursday", 6,"Friday","") <> "Saturday",
CASE(MOD( DATE(2016,01,1) - DATE(1900, 1, 6), 7), 0, "Saturday", 1, "Sunday", 2,"Monday", 3, "Tuesday", 4, "Wednesday", 5, "Thursday", 6,"Friday","") <>  "Sunday"),1,0))

you can create custom setting for holiday list and use the same in formula field.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.