software

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 quot;Jan-01-06,quot; and it
is always quot;MMM-DD-YY.quot;

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: www.excelforum.com/member.php...oamp;userid=29378
View this thread: www.excelforum.com/showthread...hreadid=512340Select one of the columns
data|text to columns
fixed width (but don't draw any lines)
tell excel that this field is a date (mdy)
and finish up.

(and do the remainder of the other columns, too.)

thekovinc wrote:
gt;
gt; Hello. I am currently having a problem with text/dates. I am importing
gt; data from another file that is completely text formatted. Some of the
gt; columns in this file contain dates in the form of quot;Jan-01-06,quot; and it
gt; is always quot;MMM-DD-YY.quot;
gt;
gt; I am trying to find the difference between two of these dates, but it
gt; will not work because of the text formatting.
gt;
gt; ie. A formula that would output the difference between Jan-16-06 and
gt; Jan-12-06 (the answer would be 4 days).
gt;
gt; I am wondering if there is any easy way to convert them to dates
gt; (changing format does not work). I have thought about just using the
gt; LEFT command to grab the middle two numbers for the date, but that does
gt; not take into consideration month or year changes.
gt;
gt; Please let me know if you can help. Thanks in advance!
gt;
gt; Nick
gt;
gt; --
gt; thekovinc
gt; ------------------------------------------------------------------------
gt; thekovinc's Profile: www.excelforum.com/member.php...oamp;userid=29378
gt; View this thread: www.excelforum.com/showthread...hreadid=512340

--

Dave Peterson

Have a look at the DateValue function.

--
Damon Longworth

2006 East Coast Excel User Conference
April 19/21st, 2006
Holiday Inn, Boardwalk
Atlantic City, New Jersey
Early Bird Registration Now Open!!
www.ExcelUserConference.com

2006 UK Excel User Conference
Summer, 2006
London, Englandquot;thekovincquot; gt; wrote
in message ...

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 quot;Jan-01-06,quot; and it
is always quot;MMM-DD-YY.quot;

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:
www.excelforum.com/member.php...oamp;userid=29378
View this thread: www.excelforum.com/showthread...hreadid=512340
Have a look at the DateValue function.

--
Damon Longworth

2006 East Coast Excel User Conference
April 19/21st, 2006
Holiday Inn, Boardwalk
Atlantic City, New Jersey
Early Bird Registration Now Open!!
www.ExcelUserConference.com

2006 UK Excel User Conference
Summer, 2006
London, Englandquot;thekovincquot; gt; wrote
in message ...

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 quot;Jan-01-06,quot; and it
is always quot;MMM-DD-YY.quot;

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:
www.excelforum.com/member.php...oamp;userid=29378
View this thread: www.excelforum.com/showthread...hreadid=512340

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

In B1 put =MID(A1,5,3)amp;LEFT(A1,4)amp;quot;20quot;amp;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/2006You can use C1 as a real date and perform math on it.--
Gary''s Studentquot;thekovincquot; wrote:

gt;
gt; Hello. I am currently having a problem with text/dates. I am importing
gt; data from another file that is completely text formatted. Some of the
gt; columns in this file contain dates in the form of quot;Jan-01-06,quot; and it
gt; is always quot;MMM-DD-YY.quot;
gt;
gt; I am trying to find the difference between two of these dates, but it
gt; will not work because of the text formatting.
gt;
gt; ie. A formula that would output the difference between Jan-16-06 and
gt; Jan-12-06 (the answer would be 4 days).
gt;
gt; I am wondering if there is any easy way to convert them to dates
gt; (changing format does not work). I have thought about just using the
gt; LEFT command to grab the middle two numbers for the date, but that does
gt; not take into consideration month or year changes.
gt;
gt; Please let me know if you can help. Thanks in advance!
gt;
gt; Nick
gt;
gt;
gt; --
gt; thekovinc
gt; ------------------------------------------------------------------------
gt; thekovinc's Profile: www.excelforum.com/member.php...oamp;userid=29378
gt; View this thread: www.excelforum.com/showthread...hreadid=512340
gt;
gt;


Thanks a lot!--
thekovinc
------------------------------------------------------------------------
thekovinc's Profile: www.excelforum.com/member.php...oamp;userid=29378
View this thread: www.excelforum.com/showthread...hreadid=512340