2

How can I calculate next working day excluding public holiday configured in my saleforce org & weekend using formula field?

Improve this question

1 Answer 1

Reset to default
2

Salesforce Holidays cant be fetched unless you go via schedule apex route.Below formula will help if you want just to exclude weekends

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)
Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .