# Excluding weekend and holidays from my 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!

• Your question title mentions holidays but your formula does not make any reference to them. Please confirm that it is just week days and weekends you are dealing with and not holidays. Apr 10, 2014 at 11:27

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

Here is one example for using it -

``````    Date txnDate = System.today();

Integer holidaysCount = 0;
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);
System.debug('The value of isHoliday is : '+isHoliday);
if (isHoliday) {
holidaysCount++;
}
}
System.debug('The holidays are : '+holidaysCount);
``````

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.

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;
}
``````