Microsoft Office Forums Need a formula to calculate turnaround time
 Register FAQ Search Today's Posts Mark Forums Read

#1
01-19-2012, 12:41 PM
 Rockitman31 Windows XP Office 2007 Novice Join Date: Sep 2011 Posts: 10
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
01-19-2012, 01:07 PM
 JBeaucaire Windows XP Office 2003 Advanced Beginner Join Date: Dec 2011 Posts: 51

B1 = completed date

C1: =MAX(0.5, INT(B1)-INT(A1))
Format C1 as General.
#3
01-19-2012, 02:10 PM
 Rockitman31 Windows XP Office 2007 Novice Join Date: Sep 2011 Posts: 10

Awesome! Thank you!

I don't understand it but it works like a charm!
#4
01-19-2012, 05:08 PM
 JBeaucaire Windows XP Office 2003 Advanced Beginner Join Date: Dec 2011 Posts: 51

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
01-23-2012, 09:31 AM
 Rockitman31 Windows XP Office 2007 Novice Join Date: Sep 2011 Posts: 10

Wow! Thank you so much for explaining this forumla in great detail to me. That is very helpful.
#6
07-05-2012, 11:21 AM
 namedujour Windows XP Office 2007 Advanced Beginner Join Date: Apr 2011 Posts: 75

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
07-05-2012, 03:40 PM
 Colin Legg Windows 7 32bit Office 2010 32bit Microsoft MVP Join Date: Jan 2011 Location: UK Posts: 381

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
07-06-2012, 10:44 AM
 namedujour Windows XP Office 2007 Advanced Beginner Join Date: Apr 2011 Posts: 75

That was just too easy! Thank you!
#9
09-07-2015, 01:50 AM
 Nina46 Windows 7 32bit Office 2007 Novice Join Date: Sep 2015 Posts: 6

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
09-07-2015, 09:41 AM
 Pecoflyer Windows 7 64bit Office 2010 64bit Moderator Join Date: Nov 2011 Location: Brussels Belgium Posts: 1,217

Hi
It's always better to start your own one, old threads not always being followed by members.
__________________
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 Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post USAOz Excel 2 09-11-2011 04:58 PM Snvlsfoal Word Tables 1 08-11-2011 05:42 AM grs Excel 3 02-21-2011 02:17 AM rkeles Excel 4 09-22-2010 12:38 AM jrasche2003@yahoo.com Excel 2 02-09-2007 07:10 AM

All times are GMT -7. The time now is 01:25 PM.

 -- Default Style -- Mobile Style Contact Us - Privacy Statement - Top