Sorting by date (different date formats)

[mr_spaceuk]

Hi,

Currently have a spreadsheet with a column that has dates in and the dates are in different formats E.G: 01.01.2010 & 1.01.2010 & 1.1.10 etc...

I have a requirement to sort this column into order but the standard sort option is not correctly sorting prob due to the different formats.

As I have over 6500 dates to sort its too much to go and change it all into the same format.

How can I sort the dates?

[teylyn]

You're probably looking at text, not dates. In order to sort them in a chronological fashion, you have to convert them to real dates first.

Depending on your system settings, you may need to replace the . in 1.1.2010 with a / like 1/1/2010. You can use a find and replace to do that.

Then use =Datevalue(A1) to convert the text in cell A1 into a date. Format the result in a date format of your choice and copy the formula down. Now you should have real dates in that column and you can sort by that column.

If that does not help, post a sample workbook and specify what the default date format is for your computer settings.