[SalesForce] Excluding weekend and holidays from the days number

I want to retreive the weekend and holidays from my days number! I've created a formula field named weekend where I put :

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

In the other hand I have days number defining this way: 

(IF(TEXT(Start_date__c)= TEXT(End_date__c) , 0.0, End_date__c - Start_date__c)) - Weekend__c

I want to combine them!

Best Answer

You can use the Business Hours object in the salesforce to attain this functionality.

Salesforce Documentation Link for Business Hours

Here is one example for using it - 

    Date txnDate = System.today();
    Date systemDate = txnDate.addDays(10);

    Integer holidaysCount = 0;
    BusinessHours bh;
    List<BusinessHours> temp = [SELECT ID, Name, IsDefault, IsActive From BusinessHours 
                                    WHERE IsDefault = true 
                                    OR Name = 'BANK'];
    for (BusinessHours b : temp) {
        if (b.IsDefault) {
            bh = b;
        }
    }
    while (txnDate != systemDate) {
        Datetime now = Datetime.newInstance(txnDate.year(), txnDate.month(), txnDate.day(), 0, 0, 0);        
        Boolean isHoliday = !BusinessHours.isWithin(bh.Id, now);
        System.debug('The value of isHoliday is : '+isHoliday);
        if (isHoliday) {
            holidaysCount++;
        }
        txnDate = txnDate.addDays(1);
    }
    System.debug('The holidays are : '+holidaysCount);
Related Topic