#1




Need a formula to calculate turnaround time
Excel 2007.
I have ran a query in SQL and copied it over to Excel. I have 2 columns. The first is a "received date" and the second is a "completed date". The format for the receive date looks like this: 09/27/2011 0:00 The format for the completed date looks like this: 09/28/2011 10:49 I am trying to calculate turnaround time base on these rules: Ignore the time stamp. If the receive date and the completed date are the same, the turnaround time will be computed as .5 days. So in the example above, the turnaround time is 1 day. Can somebody give me a quick formula to calculate this please? 
#2




A1 = received date
B1 = completed date C1: =MAX(0.5, INT(B1)INT(A1)) Format C1 as General. 
#3




Awesome! Thank you!
I don't understand it but it works like a charm! 
#4




Excel stores dates in the following manner:
40814.45069 If you enter that number into a cell, then change the cell format to DATE, it will display: 9/28/2011 10:49am Pretty slick, the whole numbers are days, and the decimal values are a fraction of the day. The INT() function will look at any number and give you the value without decimals. you said you didn't care about the times, so the INT() function effectively strips them out. So the formula INT(B1)INT(A1) will subtract the second date from the first. The result will be some whole number. Ok so far? So based on the two dates you gave, that formula calculates like so: =INT(B1)INT(A1) =INT(09/28/2011 10:49)INT(09/27/2011 0:00) =4081440813 Result: 1 Ok so far? Now, we insert the formula into a MAX() function. MAX() looks at a series of numbers and returns the highest value in the range. I fed in the first value of .5 since you said you wanted that to be the minimum. Then the INT() formula will calculate the other value, then MAX will present the higher of the two numbers. =MAX(0.5, INT(B1)INT(A1)) =MAX(0.5, 1) Result: 1 
#5




Wow! Thank you so much for explaining this forumla in great detail to me. That is very helpful.

#6




I found this thread in a search, and am delighted that it works for dates only. However, I do not want to strip out the time, and I'm not having much luck. The formula isn't changing the total when I change the minutes  although it DOES change when I change the hours.
Could someone please tell me how to modify this formula so that 7/6/12 10:22 minus 7/3/12 14:13 gives me a correct answer? Thank you! 
#7




Hi,
If A1 contains 7/6/12 10:22 and A2 contains 7/3/12 14:13 Then put this formula in A3 =A1A2 and format A3 to show both the date and time. 
#8




That was just too easy! Thank you!

#9




Im trying to calculate turnaround times too, but that formula wont work for me in some of the figures  where the time elapsed is over several months or even a year. I have used INT() to get time elapsed but when I try to get average or try sum/count i get either #DIV/0 or #Value error?

#10




Hi
please don't hijack other members's threads. It's always better to start your own one, old threads not always being followed by members. If needed uou can add a link to this thread. I will close this thread.
__________________
Appreciate the help you got here? Click on the "scales" symbol on your helper's post to give some reputation points. 
Thread Tools  
Display Modes  

Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
Formula to calculate Leap Year  USAOz  Excel  2  09112011 04:58 PM 
Calculate elapsed time in Word table?  Snvlsfoal  Word Tables  1  08112011 05:42 AM 
What formula should I use to calculate commission?  grs  Excel  3  02212011 02:17 AM 
Calculate formula base of list menu  rkeles  Excel  4  09222010 12:38 AM 
How to get cells to calculate time?  jrasche2003@yahoo.com  Excel  2  02092007 07:10 AM 