Date Sorting problem in Excel

[success_ny]

There have been many posts on this issue, but I am trying to figure out
the best/simplest solution. We use a propriatary software to export a
report results into Excel spread sheet.The resulting spread sheet has a
TEXT/General format for all columns by default.

But even if I select the date column and format it as "Date", sort by
that column does not really sort by the date year. It does an
alphanumeric sort by the first digit which is a month.

How can we sort by date so that the real date value is taken into
account. I.e., right now it sorts like this:

01/03/2000
01/04/2004
02/28/2003

And I want it to sort by date accounting for the year:

01/03/2000
02/28/2003
01/04/2004

I saw some solution where one has to copy an emopty cell into the
column which sounds kind of strange. What is the best way to solve this
problem?

 

[Gord Dibben]

Select the data.

Format to Date, which should bring no change yet.

Data>Text to Columns>Next>Next>Column Data Format>Date>MDY>Finish.

Now try the sort.


Gord Dibben MS Excel MVP