I have a user that is trying to sort a column of dates in Excel 2007. The cells are in the mm/dd/yyyy hh:mm date format. When I select the column to sort, it sorts it by month first. The user has more than one year and for some reason, Excel sorts it by month first, ignoring the year. Any suggestions? Keep in mind that this is a regular user doing the sorting. Thank you.

I'd guess that the values aren't really dates (or dates and times).

Try reformatting the cells into an unambiguous date/time format, like:
mmmm dd, yyyy hh:mm:ss

The cells that don't change display are the troublesome cells.

You (or the user) will want to address that issue before continuing with the
sort.

DEPChris wrote:

I have a user that is trying to sort a column of dates in Excel 2007. The cells are in the mm/dd/yyyy hh:mm date format. When I select the column to sort, it sorts it by month first. The user has more than one year and for some reason, Excel sorts it by month first, ignoring the year. Any suggestions? Keep in mind that this is a regular user doing the sorting. Thank you.

--

Dave Peterson

I have been doing lots of work in Excel with dates and time recently. It is my experience that Excel reads dates as dd/mm/yyyy regardless of how you try to format the date.

For example, 01/01/2010 and 01/02/2010 are read by the system as Jan 1, 2010 and Feb 1, 2010 respectively.

If anyone knows how to make the system take 01/02/2010 to mean January 2, 2010 I would love to know.

 

You need to set up the correct regional settings:

http://windows.microsoft.com/en-US/windows-vista/Change-the-display-of-dates-times-currency-and-measurements

 

To sort on dates properly you need the region set right and the date cells configured as dates.

 

I had a similar problem when I was working with data exported to Excel from another program. I set the format to date on what I wanted ot sort, and even though it appeared correctly, it was not really in the date format and it would sort in the manner that you described above.  When I checked the content of the cell however, there were extra spaces at the end of the date which caused Excel to "assume" it was text rather than a date as far as I can tell.  When I deleted the spaces at the end it corrected the issue and it would sort as desired.  I don't know if that is the cause of your problem, but you might check to see if you were having the same issue.

For those who don't have the regional settings incorrect try this.

1. select all the dates that you want to operate on
2. then do DATA|Text_to_columns
3. then FINISH

This works for me.