#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) =40814-40813 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 =A1-A2 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.
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Formula to calculate Leap Year | USAOz | Excel | 2 | 09-11-2011 04:58 PM |
Calculate elapsed time in Word table? | Snvlsfoal | Word Tables | 1 | 08-11-2011 05:42 AM |
What formula should I use to calculate commission? | grs | Excel | 3 | 02-21-2011 02:17 AM |
Calculate formula base of list menu | rkeles | Excel | 4 | 09-22-2010 12:38 AM |
How to get cells to calculate time? | jrasche2003@yahoo.com | Excel | 2 | 02-09-2007 07:10 AM |