Sort excel date column in dd-mm-yyyy format

Question

I sorted a dd-mm-yyyy date column but it doesn't gets sorted correctly.

This is how it looks like after sorting which is incorrect.

01-03-2018
02-02-2018
03-02-2018
03-03-2018
03-03-2018
04-03-2018
04-03-2018
04-12-2017
04-12-2017
05-03-2018
06-02-2018
06-12-2017

Any tips?

Solution

After selecting date and selecting sort anything that looks like a number, as a number it is sorting correctly. Earlier I was selecting sort numbers and numbers stored as text separately.

Comments

[Attie]

Have you get the data type to "date"? Works fine for me.

[Jeff Zeitlin]

Making sure that Excel thinks those are dates rather than strings is the best solution, but if for some reason they have to be strings, you should probably consider rearranging them to YYYY-MM-DD, which is guaranteed to be (a) non-ambiguous, and (b) properly sorted.

[Rajesh Sinha]

Are you sorting Lowest to Older to Newest or Newest to older?

[Mohammad Yusuf]

Tried date. Doesn't work. Have to sort in ascending. This is just a sample. There are more rows.

[Rajesh Sinha]

@MYGz, Oldest to Newest is, Ascending and Newest to Oldest is Descending. I've tried you date it's working nicely !!

[Mohammad Yusuf]

Ok after selecting date and selecting sort anything that looks like a number, as a number it is sorting correctly. Earlier I was selecting sort numbers and numbers stored as text separately.

[Rajesh Sinha]

Keep the date column either General or Short Date , basically Date value is Number, if you are talking data type.

[Lee]

Seems excel tread and sort the data as mm-dd-yyyy, where do you get the file? Go to Control Panel and check your system region, make sure you're using the dd-mm-yyyy date format as default.

[fixer1234]

In Excel, you need to distinguish format from what's stored. Excel works with what's stored, not how it looks. You can format dates to appear however you want. Excel treats them chronologically. If you want a non-chronological sort based on the order of the date components as displayed, you need to convert the date to a text translation, then sort that. Your example is text strings rather than dates, and Excel is sorting the text starting from the left. You need to convert the data to Excel dates for Excel to handle it as dates.

[phuclv]

@Lee no the above dates are in text format so they're sorted lexicographically as text. That means dates within the same year will be sorted correctly. OTOH the yyyy-mm-dd format sorts correctly as text