1

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.

1

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
  • Replace with DATEVALUE(YourCustomDateTime__c) instead.

Assumptions/Limitations/Background:

  • Weekdays are defined as Mon-Fri and weekends as Sat & Sun
  • Holidays are not addressed
  • 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)

Source: -https://help.salesforce.com/HTViewSolution?id=000004526

https://success.salesforce.com/answers?id=90630000000gv5PAAQ


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){
            dtStartDate = dtStartDate.addDays(1);            
            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.

|improve this answer|||||
  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.