Formula to calculate the number of days between two dates while excluding weekends

  • March 02, 2022

This formula will not exclude the holidays, because each organization has its own holidays based on region/country. Hence, Salesforce doesn’t have knowledge about individual lists of holidays.

  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

  1. Create a formula field that returns a number
  2. Paste in one of the two formulas
  3. Replace StartDate__c and EndDate__c with your custom field values
  4. If using date/time fields, replace with DATEVALUE(YourCustomDateTime__c)

Notes

  • Weekdays are defined as Monday through Friday and weekends as Saturday and Sunday
  • 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
  • Monday through Sunday is counted as five (5) weekdays and two (2) weekend days
  • Monday through Friday is NOT (Fri subtract Mon) = 4 elapsed days
  • Saturday through Sunday is NOT (Sun subtract Sat) = 1 elapsed day
  • If you use another formula field as the start or end date, 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 

The above formula will help you calculate the value in a negative scenario, as well.

Use case: When the start date is greater than the end date

For example: Start Date 3/22/2022 and End Date 3/16/2022 

Output =  -4

— By Dilsha Khan