Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Closed Thread
 
Thread Tools Display Modes
  #1  
Old 01-19-2012, 12:41 PM
Rockitman31 Rockitman31 is offline Need a formula to calculate turnaround time Windows XP Need a formula to calculate turnaround time Office 2007
Novice
Need a formula to calculate turnaround time
 
Join Date: Sep 2011
Posts: 10
Rockitman31 is on a distinguished road
Default 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  
Old 01-19-2012, 01:07 PM
JBeaucaire JBeaucaire is offline Need a formula to calculate turnaround time Windows XP Need a formula to calculate turnaround time Office 2003
Advanced Beginner
 
Join Date: Dec 2011
Posts: 51
JBeaucaire is on a distinguished road
Default

A1 = received date
B1 = completed date

C1: =MAX(0.5, INT(B1)-INT(A1))
Format C1 as General.
  #3  
Old 01-19-2012, 02:10 PM
Rockitman31 Rockitman31 is offline Need a formula to calculate turnaround time Windows XP Need a formula to calculate turnaround time Office 2007
Novice
Need a formula to calculate turnaround time
 
Join Date: Sep 2011
Posts: 10
Rockitman31 is on a distinguished road
Default

Awesome! Thank you!

I don't understand it but it works like a charm!
  #4  
Old 01-19-2012, 05:08 PM
JBeaucaire JBeaucaire is offline Need a formula to calculate turnaround time Windows XP Need a formula to calculate turnaround time Office 2003
Advanced Beginner
 
Join Date: Dec 2011
Posts: 51
JBeaucaire is on a distinguished road
Default

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  
Old 01-23-2012, 09:31 AM
Rockitman31 Rockitman31 is offline Need a formula to calculate turnaround time Windows XP Need a formula to calculate turnaround time Office 2007
Novice
Need a formula to calculate turnaround time
 
Join Date: Sep 2011
Posts: 10
Rockitman31 is on a distinguished road
Default

Wow! Thank you so much for explaining this forumla in great detail to me. That is very helpful.
  #6  
Old 07-05-2012, 11:21 AM
namedujour
Guest
 
Posts: n/a
Default

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  
Old 07-05-2012, 03:40 PM
Colin Legg's Avatar
Colin Legg Colin Legg is offline Need a formula to calculate turnaround time Windows 7 32bit Need a formula to calculate turnaround time Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 369
Colin Legg will become famous soon enough
Default

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  
Old 07-06-2012, 10:44 AM
namedujour
Guest
 
Posts: n/a
Default

That was just too easy! Thank you!
  #9  
Old 09-07-2015, 01:50 AM
Nina46 Nina46 is offline Need a formula to calculate turnaround time Windows 7 32bit Need a formula to calculate turnaround time Office 2007
Novice
 
Join Date: Sep 2015
Posts: 6
Nina46 is on a distinguished road
Default

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  
Old 09-07-2015, 09:41 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Need a formula to calculate turnaround time Windows 7 64bit Need a formula to calculate turnaround time Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

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
Closed Thread

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Formula to calculate Leap Year USAOz Excel 2 09-11-2011 04:58 PM
Need a formula to calculate turnaround time Calculate elapsed time in Word table? Snvlsfoal Word Tables 1 08-11-2011 05:42 AM
Need a formula to calculate turnaround time 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
Need a formula to calculate turnaround time How to get cells to calculate time? jrasche2003@yahoo.com Excel 2 02-09-2007 07:10 AM

Other Forums: Access Forums

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


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft