Formula To Calculate The Number Of Days Between Two Dates While Excluding Weekends

Author: Dilsha Khan

However it’ll not exclude the HOLIDAYS, Because each org has its own holidays based on region /country. Hence salesforce don’t have knowledge about list of holidays.

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) 

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.
Notes:
  • 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.

(5 * ( FLOOR( ( date_1 – DATE( 1900, 1, 8) ) / 7 ) ) + MIN( 5, MOD( date_1 – DATE( 1900, 1, 8), 7 ) ) ) – (5 * ( FLOOR( ( date_2 – DATE( 1900, 1, 8) ) / 7 ) ) + MIN( 5, MOD( date_2 – DATE( 1900, 1, 8), 7 ) ) )

date_2   equals to Start Date 

date_1   equals to End Date 

Above formula will help us to calculate the value in a negative scenario as well.

Case : when start date is greater than end date.

Ex: Start Date: 3/22/2022 & End Date: 3/16/2022 

Output =  -4

We use cookies on this site to enhance your user experience. For a complete overview of how we use cookies, please see our privacy policy.