Text dates to date format

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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)
 
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)
 
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.
 
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
 
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.
Click to expand...

Give this formula a try...

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

Rick
 
Back
Top