Let's say in A1 we have Jan-01-06 as a text string

In B1 put =MID(A1,5,3)&LEFT(A1,4)&"20"&RIGHT(A1,2)
you will see 01-Jan-2006

While this may not seem like a big improvement, it is something that
datevalue can handle.

In C1 put =DATEVALUE(B1) and you will see 38718
format as date to see: 1/1/2006


You can use C1 as a real date and perform math on it.


--
Gary''s Student


"thekovinc" wrote:


Hello. I am currently having a problem with text/dates. I am importing
data from another file that is completely text formatted. Some of the
columns in this file contain dates in the form of "Jan-01-06," and it
is always "MMM-DD-YY."

I am trying to find the difference between two of these dates, but it
will not work because of the text formatting.

ie. A formula that would output the difference between Jan-16-06 and
Jan-12-06 (the answer would be 4 days).

I am wondering if there is any easy way to convert them to dates
(changing format does not work). I have thought about just using the
LEFT command to grab the middle two numbers for the date, but that does
not take into consideration month or year changes.

Please let me know if you can help. Thanks in advance!

Nick


--
thekovinc
------------------------------------------------------------------------
thekovinc's Profile: http://www.excelforum.com/member.php...o&userid=29378
View this thread: http://www.excelforum.com/showthread...hreadid=512340