HI,

I have read throught some of the posts on date formats but I can't seem to
find the answer. I want to convert dates in the text format Feb 18/04 to date
format 18-Feb-04. When I try text to columns it doesn't recognize the two
digit year and results in a column with the month and and one with the day
and year.

Thanks for your help.

If the format is fixed, try


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"jenn" wrote in message
...
HI,

I have read throught some of the posts on date formats but I can't seem to
find the answer. I want to convert dates in the text format Feb 18/04 to
date
format 18-Feb-04. When I try text to columns it doesn't recognize the two
digit year and results in a column with the month and and one with the day
and year.

Thanks for your help.

Try again.

If the format is fixed, try

=DATEVALUE(MID(A21,5,2)&"-"&LEFT(A21,3)&"-"&RIGHT(A21,2))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"jenn" wrote in message
...
HI,

I have read throught some of the posts on date formats but I can't seem to
find the answer. I want to convert dates in the text format Feb 18/04 to
date
format 18-Feb-04. When I try text to columns it doesn't recognize the two
digit year and results in a column with the month and and one with the day
and year.

Thanks for your help.

In step 3 of the wizard (Data Text to Columns), under Col data format,
check "Date", then select "MDY" from the droplist. Click Finish. Then format
the col as date to taste.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"jenn" wrote:
HI,

I have read throught some of the posts on date formats but I can't seem to
find the answer. I want to convert dates in the text format Feb 18/04 to date
format 18-Feb-04. When I try text to columns it doesn't recognize the two
digit year and results in a column with the month and and one with the day
and year.

Thanks for your help.

I tried Text to Columns and entered Date as MDY and got 18-Feb-04.

Excel 2003

"jenn" wrote:

HI,

I have read throught some of the posts on date formats but I can't seem to
find the answer. I want to convert dates in the text format Feb 18/04 to date
format 18-Feb-04. When I try text to columns it doesn't recognize the two
digit year and results in a column with the month and and one with the day
and year.

Thanks for your help.

Hi,

Some work with the text to columns but they are not all exactly the same,
Feb 1/04 doesn't work because I guess it isn't Feb 01/04 and sometimes Mar is
written as March. The DATEVALUE function that bob suggested seems to work for
most of them and can be modified to work of the ones with only 1 day value.

Thanks everybody

"Toppers" wrote:

I tried Text to Columns and entered Date as MDY and got 18-Feb-04.

Excel 2003

"jenn" wrote:

HI,

I have read throught some of the posts on date formats but I can't seem to
find the answer. I want to convert dates in the text format Feb 18/04 to date
format 18-Feb-04. When I try text to columns it doesn't recognize the two
digit year and results in a column with the month and and one with the day
and year.

Thanks for your help.

Some work with the text to columns but they are not all exactly the same,
Feb 1/04 doesn't work because I guess it isn't Feb 01/04 and sometimes Mar
is
written as March. The DATEVALUE function that bob suggested seems to work
for
most of them and can be modified to work of the ones with only 1 day
value.

Give this formula a try...

=DATEVALUE(MID(A21,FIND(" ",A21)+1,FIND("/",A21)-FIND("
",A21)-1)&"-"&LEFT(A21,3)&"-"&RIGHT(A21,2))

Rick