# 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.

-
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

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)
``````
-
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
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? – LoveLace Apr 4 '14 at 15:25