Simple question: Difference between two dates?

[dstock]

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 betwee
those dates, which is obviously 5. The reason it need to keep the rang
in one cell is this will be used in a VLookup function. Thanks

 

[Guest]

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

 

[Dave Peterson]

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).