# how to calculate the number of specified week day within the date range?

I want to know the number of specified day say(Sun, Mon or Sat) within the specified date range example (11/01/2014 to 12/16/2015).

Is there any formula to calculate this, such as we pass these two dates as input and get the number of specified day between this date range?. Kindly suggest.

Purpose: Calculate the number of days between two dates while excluding weekends or excluding weekdays.

Formulas provided:

• Weekday Count Formula
• Weekend Days Count Formula

Steps to create:

• Create a formula field that returns a number.
• Paste in one of the two formulas.
• Replace StartDate__c and EndDate__c with your custom field values.
• If using Date/Time fields

Assumptions/Limitations/Background:

• Weekdays are defined as Mon-Fri and weekends as Sat & Sun
• June 24, 1985 is a long distant Monday used as a reference point
• The result will include BOTH the START and END dates.
• Mon-Sun is counted as 5 Weekdays and 2 Weekend Days.
• Mon-Fri is NOT (Fri subtract Mon) = 4 Elapsed Days.
• Sat-Sun is NOT (Sun subtract Sat) = 1 Elapsed Day.
• If you use another Formula field as the start or end dates you MAY hit a compilation limit.
• Workaround - Use workflow rules to save the output of the formula fields into a regular date field.
• Be sure to TEST the formulas FIRST.

Weekday Count Formula:

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

Weekend Days Count Formula:

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

Edit

Calculate Specific Day(Monday, Tuesday, Wednesday etc) between two dates

``````public class CalculateDayBetweenTwoDates {
public static Integer calculateDays(Date dtStartDate, Date stEndDate, String strDay){

Integer intCount = 0;
while(dtStartDate < stEndDate){
datetime myDate = datetime.newInstance(dtStartDate.year(), dtStartDate.month(), dtStartDate.day());
if(myDate.format('EEEE') == strDay){
intCount++;
}
}
return intCount;
}
}
``````

Use developer console for test

``````system.debug('======'+CalculateDayBetweenTwoDates.calculateDays(Date.today(), Date.today().addDays(16), 'Sunday'));
``````

Pass startDate, EndDate and Day It will return no of days.

• Hi Ratan, if I want to calculate only the numbers of mondays within this date range . How will I be able to do it – prasanthcheenu Dec 17 '15 at 5:18
• @prasanthcheenu instead of formula, trigger will work for calculation? – Ratan Paul Dec 17 '15 at 17:21
• @prasanthcheenu I have added logic for calculation. – Ratan Paul Dec 18 '15 at 11:29
• Cool apex code works for you. – Ratan Paul Dec 21 '15 at 7:34