custom number format to sort by year

[miconian]

My raw data contains dates in this format:

05/27/2011 12:52:46PM

(That's all in one cell.)

I need to sort this data chronologically from oldest to most recent. The sorting must work across years, so that, for example, December 2010 appears before January 2011.

Excel does recognize this format as a date (once I format the cells as a date field), but it sorts only by month, causing annoying stuff like this:

09/09/2011 2:41:00PM
09/09/2011 12:22:51PM
10/27/2010 9:45:32AM
11/05/2010 8:03:39PM

...see how it's sorting by month?

I do want it to sort by month... but only after it has sorted by year.

I have played around with applying custom date formats to this, but perhaps I'm not doing it right, or perhaps that's the wrong approach. Open to suggestions.

Thanks!

 

[Sal Paradise]

Are you sure Excel is recognizing it as a date? If it is, then earlier time is always a lower number than later times/dates. If Excel looks at it as text, however, that leading month is what it will sort by.

 

[GTO]

Are you sure that Excel is recognizing the vals as dates? Widen the column and see if it they end up on the right and/or look in the formula bar. Regardless of how the cells are formatted, is the value in the formula bar in default date format? Finally, you can select one of these cells and change the formatting to Number and it should return 40k+

 

[miconian]

Okay, you guys got me... I guess it is formatted as text. The date appears left-justified in the cell, and the formula bar shows exactly what's in the cell.

So... the question remains... how do I solve the problem?

 

[miconian]

Actually, I was wrong. The data *IS* formatted as dates... on the raw data worksheet.

I figured this out. Thanks everyone.