login

Tip of the Week

Tip of the Week – Calculate business days between two dates in Salesforce

Calendar

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

, , , , ,

2 Responses to Tip of the Week – Calculate business days between two dates in Salesforce

  1. Bobby August 27, 2019 at 6:12 am #

    Thank you !

Trackbacks/Pingbacks

  1. Count down to the New Year with our Top 10 Salesforce Tips of 2014 - December 26, 2014

    […] 2. Calculate business days between two dates in Salesforce […]

Leave a Reply