2

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.

1

1 Answer 1

5

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)
5
  • Holy cow... I'm too sober to understand the mad genius behind that one, maybe I'll figure it out as a weekend homework :D Amazing. And scary.
    – eyescream
    Dec 11, 2012 at 23:42
  • I can't understand it all, but it worked like a charm! Thank you! Now to take it a step further, what if the turnaround time you are trying to capture in business days is off of date/time fields? Not just date fields? Date/Time Opened versus Date/Time closed.... Dec 12, 2012 at 15:29
  • 1
    You are my hero - thank you so much for your help on this!!! Dec 14, 2012 at 20:31
  • Most welcome :)!!! Happy I was able to help you out!!
    – Rao
    Dec 14, 2012 at 21:11
  • Hey all! Yep it worked well, I have a problem concerning a half days! I want to count a half days as well! any idea?
    – LoveLace
    Apr 4, 2014 at 15:25

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .