Weird Excel problem never encountered: Wont sort properly by date

I exported an adhoc report from a tool into Excel. The date column wasn’t formatted as a date column, so I did that, and then tried to sort the table by date.

Excel is sorting by the month only, so that the end result looks like this small snapshot.


WTF? I’m scratching my head here. The format I picked matches mm/dd/yyyy. (It actually says “3/14/01” for the example.)

I’ve been using Excel forever and can’t figure this weird one out at all.

Check the format of the cells. If they have somehow been stored as text instead of dates, it could sort that way.

To check their specific data-type format, highlight the cells, right click and select “Format Cells…” The setting you are looking for will be on the “Number” tab.

I already formatted the column as a date. Of course it’s a date.

Anyway, I Googled around and the answer lies iin going to the Data tab, then the “Text to Columns” setting, then on step 3 of the Delimited option, changing the data type to date and selecting the correct D/M/Y option, even though right clicking told me it was already a date format, after you know, I formatted it.

Now I can sort properly. I’ve never seen THAT before.

Excel never ceases to amaze me with its quirks.

Anecdote since the question has been answered: The new versions of Excel have cell styles which allow you to set specific standards formatting and use those formats easily across many different cels and sheets in the same workbook. Now I never use the things because they are pretty much worthless to me. I regularly work with a large Excel spreadsheet for work that has massive amounts of very important data in it. One day, the nearly useless cell styles got corrupted and then the file reached the limit of how many cell styles are allowed (a ridiculously large amount) and stopped letting people add data. There is a script you can download that fixes the corrupt cell styles problem but our IT department wouldn’t allow it. I ended up having to create a new file, copy and paste the data into the new file and verify it all while deleting the corrupt cell styles that had attached themselves to the data while I copied and pasted. It took way too long.

I think I’ve had that problem in the past. I put the figure “1” in a spare cell, copied and paste/special/values/multiply it to the date column. This seemed to make them behave as dates.

More explanation - it isn’t the format that is important, it’s the data type. Excel thought your fields were text. You can tell it to format it like a date all you want, but it’s still going to think it’s text and sort it accordingly.

The solution, as you found, is to convert it into a date (numeric). You can do this with the “Text to Columns” action, performing some arithmetic function like Dahu mentioned, or a variety of other ways. One way that often works for me is to copy the column then “paste as values”. Excel will usually give me a little prompt to convert the fields to a date.

Yeah, as you have found you can change the format of the cell but Excel doesn’t always change the data in the cells to the format you want. I deal a lot with an ID field that is actually text but a large majority are only numbers. Of course if a file comes in as general it doesn’t match up to other data I may want to link to. I can change the format of the cells to text but unless you double click on the cell it doesn’t really change it.

I have written a macro that fixes it and adds any leading zeros but you would think they would have worked that out in basic Excel formatting.

I’ve had similar problems with data coming in from an external system.

Dahu’s trick would probably work.

So if you had text fields, it was stored as text…

if it thinks its a date, it converts it to days since 1901.

Excel’s sort is done based on what is stored, not what is displayed at the moment.