transform text dates into Excel dates

[Guest]

Hell

I currently have a spreadsheet with hundreds of entries wheremany of the date entries were done textually ("21 Nov. 2002"
But I need them to be date format Excel won't let me change the format/ won't recognize the text entries as a valid date entry. How can I convert these entries so the Excel will permit me to change the format and recognize the entries as valid dates?

Thank yo

Daniel

 

[Guest]

Try doing data - text to columns to separate the day, month and year. Then concatenate the values in order with a "/" in between so that it'll be a valid entry.

 

[Gord Dibben]

Daniel

Data>Text to Columns>Delimited>Next>Next. Column Data Format>Date>DMY>Finish.

No need to split and concatenate.

Gord Dibben Excel MVP

 

[Dave Peterson]

One more option (depending on how the month abbreviations are used).

I could just select that range/column and
edit|replace
what: . (period)
with what: (leave blank)
replace all.

xl2002 (usa version) converted them to dates--I did have to reformat to show the
way I like.