Tip of the Week – Calculate business days between two dates in Salesforce
You want to know how many days it takes from a Lead coming in to your system until it is converted. Or maybe you want to know how long it takes from when an Opportunity is created until it is closed. You can write a simple formula which will give you the number of days, but that’s going to include every day, including weekends, and that’s just not helpful. “How many days does it take for my team to close an Opportunity?” Well, plug in the formula below and you’ll have all the answers you need!
This formula looks hard, but it really is plug-and-play. Create a new formula field, replace Date_Received__c and Date_Finished__c with the relevant date fields in your system, and you’re ready to start reporting! This formula will output the number of weekdays between the Date Received and Date Finished dates. It really is that simple to update your metrics!
ABS(
CASE(MOD (datevalue( Date_Received__c)- DATE(1985,6,24),7),
0 , CASE( MOD( Datevalue( Date_Finished__c ) - datevalue(Date_Received__c)
,7),1,2,2,3,3,4,4,5,5,5,6,5,1)
,
1 , CASE( MOD( Datevalue( Date_Finished__c ) - datevalue(Date_Received__c)
,7),1,2,2,3,3,4,4,4,5,4,6,5,1),
2 , CASE( MOD( Datevalue( Date_Finished__c ) - datevalue(Date_Received__c)
,7),1,2,2,3,3,3,4,3,5,4,6,5,1),
3 , CASE( MOD( Datevalue( Date_Finished__c ) - datevalue(Date_Received__c)
,7),1,2,2,2,3,2,4,3,5,4,6,5,1),
4 , CASE( MOD( Datevalue( Date_Finished__c ) - datevalue(Date_Received__c)
,7),1,1,2,1,3,2,4,3,5,4,6,5,1),
5 , CASE( MOD( Datevalue( Date_Finished__c ) - datevalue(Date_Received__c)
,7),1,0,2,1,3,2,4,3,5,4,6,5,0),
6 , CASE( MOD( Datevalue( Date_Finished__c ) - datevalue(Date_Received__c)
,7),1,1,2,2,3,3,4,4,5,5,6,5,0),
999)
+
(FLOOR((( Date_Finished__c ) - ( Date_Received__c) )/7)*5)-1 +
( Date_Finished__c - Date_Received__c) - (datevalue( Date_Finished__c ) -
datevalue(Date_Received__c))
)
-Jared and the Salesforce Guys
CEO of CloudMyBiz Salesforce CRM consulting services with a deep knowledge in the lending industry. Taking keen interest in the project management side of operations, playing a vital role in the 31% YOY company growth. Strategic leader, mastering the ability to problem solve at every level of the business, providing effective solutions for clients.