Text dates to date format

[jenn]

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.

 

[Bob Phillips]

If the format is fixed, try


--
HTH

Bob

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

 

[Bob Phillips]

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)

 

[Max]

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.

 

[Toppers]

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

Excel 2003

 

[jenn]

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

 

[Rick Rothstein \(MVP - VB\)]

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