Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 01-19-2012, 11:41 AM
Rockitman31 Rockitman31 is offline Windows XP Office 2007
Novice
 
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?
Reply With Quote
  #2  
Old 01-19-2012, 12:07 PM
JBeaucaire JBeaucaire is offline Windows XP 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.
Reply With Quote
  #3  
Old 01-19-2012, 01:10 PM
Rockitman31 Rockitman31 is offline Windows XP Office 2007
Novice
 
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!
Reply With Quote
  #4  
Old 01-19-2012, 04:08 PM
JBeaucaire JBeaucaire is offline Windows XP 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
Reply With Quote
  #5  
Old 01-23-2012, 08:31 AM
Rockitman31 Rockitman31 is offline Windows XP Office 2007
Novice
 
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.
Reply With Quote
  #6  
Old 07-05-2012, 11:21 AM
namedujour namedujour is offline Windows XP Office 2007
Advanced Beginner
 
Join Date: Apr 2011
Posts: 59
namedujour is on a distinguished road
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!
Reply With Quote
  #7  
Old 07-05-2012, 03:40 PM
Colin Legg's Avatar
Colin Legg Colin Legg is offline Windows 7 32bit Office 2010 32bit
Microsoft MVP
 
Join Date: Jan 2011
Location: UK
Posts: 382
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.
Reply With Quote
  #8  
Old 07-06-2012, 10:44 AM
namedujour namedujour is offline Windows XP Office 2007
Advanced Beginner
 
Join Date: Apr 2011
Posts: 59
namedujour is on a distinguished road
Default

That was just too easy! Thank you!
Reply With Quote
Reply
Please reply to this thread with any new information or opinions.

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
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 01: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 06:10 AM


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


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft