function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Wendy M GriffinWendy M Griffin 

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 )

hpereirahpereira

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.

 

public Integer calculateWorkingDaysBetweenTwoDates(Date date1,Date date2){

               List<Holiday> holidays=[Select h.StartTimeInMinutes, h.Name, h.ActivityDate From Holiday h];

                Integer allDaysBetween = date1.daysBetween(date2);
                Integer allWorkingDays=0;
                for(Integer k=0;k<allDaysBetween ;k++ ){
                    if(checkifItisWorkingDay(date1.addDays(k),holidays)){
                        allWorkingDays++;
                    } 
                }

                return allWorkingDays;

      }

public boolean checkifItisWorkingDay(Date currentDate,List<Holiday> holidays){
                 Date weekStart  = currentDate.toStartofWeek();
                for(Holiday hDay:holidays){
                        if(currentDate.daysBetween(hDay.ActivityDate) == 0){
                                 return false;
                        }
                }
               if(weekStart.daysBetween(currentDate) ==0 || weekStart.daysBetween(currentDate) == 6){
                       return false;
                } else 
                       return true;
  }

 
Ralf Hamester (MSC)Ralf Hamester (MSC)
Hello WendyMC,

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?
 
- Ralf