[SalesForce] Calculating Turnaround Time in Date Fields

Does anyone know if there is a formula to calculate the turnaround time between 2 date fields and exclude weekend days?

Ex: Case opened 11/30/12, case closed 12/4/12 – how many days did it take to close the case excluding Sat and Sun.

Best Answer

Check this post : http://success.salesforce.com/questionDetail?qid=a1X30000000KC20EAG (look all way below for the comment posted by NZgon)

I tried the code and works too just in case : This gives you the difference between 2 days as number excluding the weekends ( make sure the formula field is a number field) 

CASE(MOD( StartDate__c - DATE(1985,6,24),7), 

0 , CASE( MOD( EndDate__c - StartDate__c ,7),1,2,2,3,3,4,4,5,5,5,6,5,1), 
1 , CASE( MOD( EndDate__c - StartDate__c ,7),1,2,2,3,3,4,4,4,5,4,6,5,1), 
2 , CASE( MOD( EndDate__c - StartDate__c ,7),1,2,2,3,3,3,4,3,5,4,6,5,1), 
3 , CASE( MOD( EndDate__c - StartDate__c ,7),1,2,2,2,3,2,4,3,5,4,6,5,1), 
4 , CASE( MOD( EndDate__c - StartDate__c ,7),1,1,2,1,3,2,4,3,5,4,6,5,1), 
5 , CASE( MOD( EndDate__c - StartDate__c ,7),1,0,2,1,3,2,4,3,5,4,6,5,0), 
6 , CASE( MOD( EndDate__c - StartDate__c ,7),1,1,2,2,3,3,4,4,5,5,6,5,0), 
999) 
+ 
(FLOOR(( EndDate__c - StartDate__c )/7)*5)

EDIT TO ANSWER IF THE FIELDS ARE DATE TIME VALUES: As per the posting above change all the date values to DateVALUE(Date) in short change all the startDate_c to DATEVALUE(startdate_c) and EndDate_c to DATEVALUE(EndDate_c), doing replace all in notepad++ will avoid the hazzle of figuring which bracket you missed.

CASE(MOD( DATEVALUE(startDate__c) - DATE(1985,6,24),7), 

  0 , CASE( MOD( DATEVALUE(EndDate__c) - DATEVALUE(startDate__c) ,7),1,2,2,3,3,4,4,5,5,5,6,5,1), 
  1 , CASE( MOD( DATEVALUE(EndDate__c) - DATEVALUE(startDate__c) ,7),1,2,2,3,3,4,4,4,5,4,6,5,1), 
  2 , CASE( MOD( DATEVALUE(EndDate__c) - DATEVALUE(startDate__c) ,7),1,2,2,3,3,3,4,3,5,4,6,5,1), 
  3 , CASE( MOD( DATEVALUE(EndDate__c) - DATEVALUE(startDate__c) ,7),1,2,2,2,3,2,4,3,5,4,6,5,1), 
  4 , CASE( MOD( DATEVALUE(EndDate__c) - DATEVALUE(startDate__c) ,7),1,1,2,1,3,2,4,3,5,4,6,5,1), 
  5 , CASE( MOD( DATEVALUE(EndDate__c) - DATEVALUE(startDate__c) ,7),1,0,2,1,3,2,4,3,5,4,6,5,0), 
  6 , CASE( MOD( DATEVALUE(EndDate__c) - DATEVALUE(startDate__c) ,7),1,1,2,2,3,3,4,4,5,5,6,5,0), 
  999) 
  + 
  (FLOOR(( DATEVALUE(EndDate__c) - DATEVALUE(startDate__c) )/7)*5)
Related Topic