Dates formatted as text

T

thekovinc

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
 
D

Dave Peterson

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

Damon Longworth

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, England


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 "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
 
D

Damon Longworth

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, England


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 "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
 
G

Gary''s Student

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