Excluding weekend and holidays from my days number

Question

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!

Answer 1

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);

Answer 2

As:

weekdays + weekenddays = calendardays

I am unclear what your problem is: given two of these you can always have a formula to get the third.

You've probably seen this already but here is a standard reference for Formula - Weekend Days or Workdays Calculation.

Answer 3

I wrote a method to not allow Task due date to not fall on the weekends, it may point you in the right direction..

public static Date changeWeekendTaskDueDate(Date thedate)
{
    Datetime dt = DateTime.newInstance(thedate, Time.newInstance(0, 0, 0, 0));
    String dayOfWeek=dt.format('E');
    Integer offset = 0;

    if(dayOfWeek == 'Sat')
    {
        offset = 2;
    }
    else if(dayOfWeek == 'Sun')
    {
        offset = 1;
    }

    return thedate + offset;
}