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
monkeykingmonkeyking 

Logic for calculating difference between two dates excluding weekends

Hello All,

I have a query regarding the login in calculating the difference between two dates excluding weekends.
I have a formula field in our org, which calculates the difference between two dates(Closed date - opened date). But this formula field also includes weekends.

I require a formula which should calculate the difference between two dates exlcuding weekends.

Please help me.

Thank you.
Shubham_KumarShubham_Kumar
Hi Rajkumar

i think this is actually inthe salesforce document
https://resources.docs.salesforce.com/218/latest/en-us/sfdc/pdf/salesforce_useful_formula_fields.pdf

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

Hope this helps, Please mark this as the best solution this helps

Thanks
Shubham Kumar 
monkeykingmonkeyking

Hello Shubham,

Thanks or your reply.

When i try this i am getting the below error,
User-added image
Shubham_KumarShubham_Kumar
Hi RajKumar

This is because CloseDate is a date field while CreatedDate is a Date/Time Field 
Try this formula 

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

Hope this helps, Please mark this as the best solution this helps

Thanks
Shubham Kumar
monkeykingmonkeyking
Hello Shubham,

Still i am getting the same error.

User-added image
Shubham_KumarShubham_Kumar
Hi Raj

I assume CreatedDate and CloseDate both are opportunity`s standard fields here. 
Can you please try this again and let me know.

CASE(MOD( DATEVALUE(CreatedDate) - DATE(1985,6,24) ,7), 
0 , CASE( MOD( CloseDate - DATEVALUE(CreatedDate) ,7),1,2,2,3,3,4,4,5,5,5,6,5,1), 
1 , CASE( MOD( CloseDate - DATEVALUE(CreatedDate),7),1,2,2,3,3,4,4,4,5,4,6,5,1), 
2 , CASE( MOD( CloseDate - DATEVALUE(CreatedDate),7),1,2,2,3,3,3,4,3,5,4,6,5,1), 
3 , CASE( MOD( CloseDate - DATEVALUE(CreatedDate),7),1,2,2,2,3,2,4,3,5,4,6,5,1), 
4 , CASE( MOD( CloseDate - DATEVALUE(CreatedDate),7),1,1,2,1,3,2,4,3,5,4,6,5,1), 
5 , CASE( MOD( CloseDate - DATEVALUE(CreatedDate),7),1,0,2,1,3,2,4,3,5,4,6,5,0), 
6 , CASE( MOD( CloseDate - DATEVALUE(CreatedDate),7),1,1,2,2,3,3,4,4,5,5,6,5,0), 999) + (FLOOR(( CloseDate - DATEVALUE(CreatedDate))/7)*5)
monkeykingmonkeyking
Hello Shubham,

Sorry for the late reply.
Both the fields are Case object fields not opportunity fields.

Thank you.

Regards,
Rajkumar CV