Text to Date format in Excel 1

[johnbarr44]

I have a spreadsheet with hundreds of dates that were entered as text - e.g. 20 Nov 1920, and also 20 November 1920 etc.

I would like to convert these cells to date format so they may be sorted (by date).

Is it possible to do this, or will all data have to be re-entered as 'date' format ??

 

[SkipVought]

Hi,

In an empty cell, enter the number 1

COPY this cell

Select the cell range with your text dates

Edit > Paste Special -- MULTIPLY

Vola y'all!

It should convert most of them.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[johnbarr44]

Wonderful !!

Worked a treat.

Many Thanks SkipVought

 

[SkipVought]

...and check out the FAQ faq68-5827.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[johnbarr44]

Thanks again, SkipVought.
Yes, an interesting summary.

I also discovered, belatedly, that Excel IS able to sort dates entered as text by choosing the "treat as numbers" choice when 'Sort' is selected. However, your method gives a much tidier result.