Simple question: Difference between two dates?
 

Suppose cell A2 has 6/27/05 - 7/1/05
A3 has 7/4/05 - 7/8/05... and so
I need to know the formula to coomputer the number of days between
those dates, which is obviously 5. The reason it need to keep the range
in one cell is this will be used in a VLookup function. Thanks!


--
dstock
------------------------------------------------------------------------
dstock's Profile: http://www.excelforum.com/member.php...o&userid=24225
View this thread: http://www.excelforum.com/showthread...hreadid=381328

Anyway you slice it, you have to get the 2 dates seperated...

So you can have one column that shows them together for the VLookup '6/27/05
- 7/1/05' and 2 more for the first and second dates...

OR you can write some tricky VB code to parse out the dates from the string,
then do the 'date arithmatic' to find your value.

I would choose the multiple column method.. Keeps the logic more straight
forward and reduces custom code. If you're concerned about redundancies or
extra columns, extra columns can be hidden AND remember, excel is NOT a data
management tool (like a database) it's a tool for analysis. (ie not really
designed to 'normalize' all the data.

HTH

"dstock" wrote:


Suppose cell A2 has 6/27/05 - 7/1/05
A3 has 7/4/05 - 7/8/05... and so
I need to know the formula to coomputer the number of days between
those dates, which is obviously 5. The reason it need to keep the range
in one cell is this will be used in a VLookup function. Thanks!


--
dstock
------------------------------------------------------------------------
dstock's Profile: http://www.excelforum.com/member.php...o&userid=24225
View this thread: http://www.excelforum.com/showthread...hreadid=381328

I agree with what Tom wrote--put those dates in their own cells. It'll make
life much simpler.

But if you can't:

=DATEVALUE(MID(A1,SEARCH(" - ",A1)+3,255))
-DATEVALUE(LEFT(A1,SEARCH(" - ",A1)-1))
(All one cell)

And if your dates are formatted consistently with your windows setting (mdy in
both spots).

dstock wrote:

Suppose cell A2 has 6/27/05 - 7/1/05
A3 has 7/4/05 - 7/8/05... and so
I need to know the formula to coomputer the number of days between
those dates, which is obviously 5. The reason it need to keep the range
in one cell is this will be used in a VLookup function. Thanks!

--
dstock
------------------------------------------------------------------------
dstock's Profile: http://www.excelforum.com/member.php...o&userid=24225
View this thread: http://www.excelforum.com/showthread...hreadid=381328

--

Dave Peterson