+ Start a Discussion
fredkafredka 

Exclude Weekend from Date Calculation

Has anyone had any luck creating a formula field that calculates the difference between two dates but does not include weekends in the calculation?  I have searched and did find one example, however, it does not work for every date... It has to do with the rounding... This is what I have so far:

 

 

IF(AND(MOD( Date_Released_to_Sales__c -DATE(1900,1,7),7)< (Date_Released_to_Sales__c- Date_to_U_W__c), ((Date_Released_to_Sales__c- Date_to_U_W__c)<7)),((Date_Released_to_Sales__c- Date_to_U_W__c)-2), ((Date_Released_to_Sales__c- Date_to_U_W__c)-(2 * ((Date_Released_to_Sales__c- Date_to_U_W__c)/7))))

 

 In some instances, however, the timeframe is off a day.  I think it has to do with the rounding of the calculation of

((Date_Released_to_Sales__c- Date_to_U_W__c)-(2 * ((Date_Released_to_Sales__c- Date_to_U_W__c)/7))))

 

I thought I might need to use ceiling but that did not solve the problem either....

 

Any help would be greatly appreciated!!

 

Fred


 

michaellatideasmichaellatideas

I found this formula in SFDC help at some point:

 

CASE(MOD( Start__c - DATE(1985,6,24),7),
0 , CASE( MOD( End__c - Start__c ,7),1,2,2,3,3,4,4,5,5,5,6,5,1),
1 , CASE( MOD( End__c - Start__c ,7),1,2,2,3,3,4,4,4,5,4,6,5,1),
2 , CASE( MOD( End__c - Start__c ,7),1,2,2,3,3,3,4,3,5,4,6,5,1),
3 , CASE( MOD( End__c- Start__c ,7),1,2,2,2,3,2,4,3,5,4,6,5,1),
4 , CASE( MOD( End__c - Start__c ,7),1,1,2,1,3,2,4,3,5,4,6,5,1),
5 , CASE( MOD( End__c - Start__c ,7),1,0,2,1,3,2,4,3,5,4,6,5,0),
6 , CASE( MOD( End__c - Start__c ,7),1,1,2,2,3,3,4,4,5,5,6,5,0),
999)
+
(FLOOR(( End__c - Start__c )/7)*5)