Excel sort date order
If you have encountered the problem where the Excel sort date order does not make sense i.e. it does not show the dates chronologically, the reason is more than likely that what you see as a date in Excel is NOT being seen as a date by Excel. When it is sorted it will be sorted as if it is text and not a date e.g.
WRONG ORDER
Jan 1 2014
Jan 10 2014
Jan 11 2014
Jan 2 2014
Jan 21 2014
RIGHT ORDER
01-Jan-14
02-Jan-14
10-Jan-14
11-Jan-14
21-Jan-14
We need to step back a moment to understand how dates work in Excel.
If you have a valid date and you format it as a general number you will see something like 40 567. This is what Excel uses as a date and it means that the date is 40 567 days from the 1st January 1900.
The fact that you format it into a proper date format is only for humans benefit. Excel is happy with the number. In fact it has to have a number! Once the date is a number it is easy for Excel to get the sort order correct. But when it is considered text it struggles as shown above.
If Excel doesn’t recognise the date as a date (e.g. I use American format of mm/dd/yyyy instead of what my computer needs of dd/mm/yyyy) it becomes text and no matter what you do it will not allow any date functions or sort orders to work.
So what you first have to do is check whether the cell is a valid date. The easiest way to do this is to try and change the format of the cell to a different date format.
So the steps would be.
- Click on the cell that contains the ‘date’
- Right click and choose the ‘Format Cells’ option
- Click on the date category
- While watching what happens in the Sample section, alternate between the different date formats (25/12/2014, 25 Dec 2014 etc)
- If the sample changes to the format you have clicked on it is a date
- If it does not change it is NOT a date.
Before you can create a valid excel date sort order, you need to convert it to a valid date. There are many ways to do this covered in the Handling Dates section.