Cnverting text date into Excell date

[Gildasl]

Hi,

I have a cell filled with date in a text format like "May 8 2011" or "Jun 6 2008" and need to convert them into excel cell date format such as "05/08/11" or "06/06/08".

Unfortunately the lookup function work only for data sorted in ascending order.

Thanks in advance for your help.

 

[Rick Rothstein]

Give this formula a try...

=1*REPLACE(A1,LEN(A1)-4,0,",")

You will then be able to apply any date format you want, although that may not be necessary... leave the offset number returned by the formula if you only need this for numerical ordering purposes.

 

[Rick Rothstein]

Give this formula a try...

=1*REPLACE(A1,LEN(A1)-4,0,",")

You will then be able to apply any date format you want, although that may not be necessary... leave the offset number returned by the formula if you only need this for numerical ordering purposes.

If you wanted to convert the data within the cell they occupy (rather than use a formula in another cell), select the column of "text dates" and call up the Convert Text To Columns dialog box, select "Delimited" on Step 1, make sure the "Space" checkbox is not checked in Step 2, then select the "Date" option button in Step 3 and make sure its drop-down has "MDY" selected and, finally, click the "Finish" button.

 

[Gildasl]

Thanks Rick. That works nicely. I like very much the "Convert Text to columns" wizard.
Gildas

 

[RedMonkey]

I was just wrestling with some horrible imported data I needed to convert that was thousands of lines long and didn't respond to format painting or formulas well and I just bumped into this post.

Thanks alot!