Need help in converting text string based date to excel date format

[rmkanneganti]

Hello all,

I have a column of about 20000 date entries and all of them are in text(string) format. May 4, 2006 is the format and I need to convert it to May 4, 2006 but in date format so that I can filter and use the data. Any help would be greatly appreciated. Thanks!

SS

 

[Vacuum Sealed]

May 4, 2006

Hi

Don't know which XL Ver. your using, but I just changed ( using 2010 )
the column format to Date/Long and it was fine.

If you are using an earlier Ver. that does not seem to want to play the
game that way, you could use a Helper Column and format it and your
column in question as Date/Long and have the helper column's formula =
("A:A") (Change target column to suit).

Then do a Copy/PasteSpecial: ValueOnly Paste of the Helper Column into
your target column, then delete the helper column and that should fix it.

Now if your needing to do this every time you import and append new data
then you will most likely need to do it via VB.

HTH
Mick.

 

[joeu2004]

I have a column of about 20000 date entries and all of them
are in text(string) format. May 4, 2006 is the format and I
need to convert it to May 4, 2006 but in date format so that
I can filter and use the data.

Select the column of cells, then use Text To Columns feature. How you get
there depends on what version of Excel you are using. After you click on
Text To Columns, select Delimited, click on Next and be sure that only Tab
is checkmarked, click on Next, then click on Date for the "Column data
format" and select the order of your dates (MDY), then click Finish.

If the dates do not appear as you wish, with the column of cells still
selected, right-click and click on Format Cells, click on the Number tab,
and select the Date format and Type or select the Custom format and enter
the desired format, perhaps "mmm d, yyyy" without quotes.