Take the 2-minute tour ×
Salesforce Stack Exchange is a question and answer site for Salesforce administrators, implementation experts, developers and anybody in-between. It's 100% free, no registration required.

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.

share|improve this question
    
I've created a similar question to address the same problem from Apex - Days between two DateTime values excluding weekends –  Daniel Ballinger Dec 12 '12 at 19:43

1 Answer 1

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)
share|improve this answer
    
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 '12 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.... –  Paula Rogers Dec 12 '12 at 15:29
1  
You are my hero - thank you so much for your help on this!!! –  Paula Rogers Dec 14 '12 at 20:31
    
Most welcome :)!!! Happy I was able to help you out!! –  rao Dec 14 '12 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? –  TrinityLoveLace Apr 4 at 15:25

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.