Formula: Business Days between dates

Have you ever needed to get the number of business days between two dates? Here is a quick formula for you to do this.

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)

 

Credits to Julie Nguyen from the answers community for the tip.

 

Getting the day of the week using Formulas

If you need to get the day of the week using formulas in Salesforce you can use the following:

MOD( CustomDate__c - DATE(1900, 1, 7), 7)

The output will be a number from 0 to 6 with the following meaning:

0 = Sunday 1 = Monday 2 = Tuesday 3 = Wednesday 4 = Thursday 5 = Friday 6 = Saturday

You can then use the following to generate the actual word:

CASE(MOD( CustomDate__c - DATE(1900, 1, 7), 7) , 0 , 'Saturday' , 1 , 'Sunday' ,  2 , 'Monday' , 3 , 'Tuesday' , 4 , 'Wednesday' , 5 , 'Thursday' , 6 , 'Friday'  , '')