function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ 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)