You need to sign in to do that
Don't have an account?
Days to Close calculation based on business days and excluding holidays for multiple countries
Hello all,
My org is used globally and I need to be able to calculate the number of days to close a case based on business days, but also take into consideration the holidays of each country (approx. 25 countries). Currently I have a formula field (as shown below), but it only contains US Holidays. Is it possible to add criteria into the existing formula so that IF Country = Australia, then calculate based on those dates, or IF Country = France, then calculate based on those specific dates? Or would a separate field be needed per country? Apex coding? There must be some way to do this, I just don't know what the solution is? There must be other orgs out there with a similar issue and hopefully someone has found a solution. Any help is greatly appreciated!
Thank you,
Wendy
Customer_Close_Date__c - DATEVALUE(Date_Issue_Received__c)
- IF ( AND(Customer_Close_Date__c >= DATEVALUE("2010-01-01"), DATEVALUE(Date_Issue_Received__c) <= DATEVALUE("2010-01-01")) , 1, 0)
- IF ( AND(Customer_Close_Date__c >= DATEVALUE("2010-05-31"), DATEVALUE(Date_Issue_Received__c) <= DATEVALUE("2010-05-31")) , 1, 0)
- IF ( AND(Customer_Close_Date__c >= DATEVALUE("2010-09-06"), DATEVALUE(Date_Issue_Received__c) <= DATEVALUE("2010-09-06")) , 1, 0)
- IF ( AND(Customer_Close_Date__c >= DATEVALUE("2010-11-25"), DATEVALUE(Date_Issue_Received__c) <= DATEVALUE("2010-11-25")) , 1, 0)
- IF ( AND(Customer_Close_Date__c >= DATEVALUE("2010-11-26"), DATEVALUE(Date_Issue_Received__c) <= DATEVALUE("2010-11-26")) , 1, 0)
- IF ( AND(Customer_Close_Date__c >= DATEVALUE("2010-12-24"), DATEVALUE(Date_Issue_Received__c) <= DATEVALUE("2010-12-24")) , 1, 0)
- CASE( MOD(DATEVALUE(Date_Issue_Received__c) - DATEVALUE( "1985-06-24" ) , 7) , 0
, CASE( MOD( Customer_Close_Date__c - DATEVALUE (Date_Issue_Received__c), 7 ),1,0,2,0,3,0,4,0,5,1,6,2,0 ), 1
, CASE( MOD( Customer_Close_Date__c - DATEVALUE (Date_Issue_Received__c), 7 ),0,0,1,0,2,0,3,0,4,0,5,2,2 ), 2
, CASE( MOD( Customer_Close_Date__c - DATEVALUE (Date_Issue_Received__c), 7 ),0,0,1,0,2,0,3,1,2), 3
, CASE( MOD( Customer_Close_Date__c - DATEVALUE (Date_Issue_Received__c), 7 ),0,0,1,0,2,1,2), 4
, CASE( MOD( Customer_Close_Date__c - DATEVALUE (Date_Issue_Received__c), 7 ),0,0,1,1,2), 5
, CASE( MOD( Customer_Close_Date__c - DATEVALUE (Date_Issue_Received__c), 7 ),0,1,2), 6
, CASE( MOD( Customer_Close_Date__c - DATEVALUE (Date_Issue_Received__c), 7 ),6,2,1)
, 666 )
- ( FLOOR( ( Customer_Close_Date__c - DATEVALUE (Date_Issue_Received__c) ) / 7 ) * 2 )
Hello WendyMc,
I would create a custom setting to store the holidays and then query it to know how many holidays exist between the 2 dates for a specific country.
Check out this code that uses the Holiday object (does not enable a per country configuration).
You could have something very similar for the custom setting.
may I ask you if your formula stated above respect that holidays may be a Saturday or Sunday? I have invented the same wheel and have now the problem that holidays count double when they are Saturdays or Sundays. Any solution for that?
Did the solution posted by hpereira help you for your problem?