ExcelBanter - View Single Post

Ron Rosenfeld

On Mon, 12 May 2008 06:14:11 -0700 (PDT), wrote:

Hello,

I have a spreadsheet of weekly reported data going back 3 years.
Currently the dates are reported in mm/dd/yyyy format and I want to be
able to sort the activities chronologically. Is there any way I can
apply a yyyy/mm/dd mask to my date column so that the SORT function
will see these in the correct order?

I am using Excel 2003 and the only date formats it gives me the option
to select have the year at the end of the entry (therefor it is
sorting by year or month and not doing a proper chronological sort).

Many thanks,

Ryan

Give an example of how your dates are sorting, and how you wish them to sort.

It sounds as if your dates may be stored as text, and not as "Excel Dates". It
may even be the case that some cells are text, and others are "Excel Dates".

Excel stores dates as sequential numbers, with 1/1/1900 = 1. The values should
sort chronologically (numerically) regardless of how they are formatted.

So the first thing is to determine what you have in your cells.

In addition to the above, please post the result of the function
=ISNUMBER(cell_ref) where cell_ref contains one of these mis-sorted dates. Do
so for several dates at the beginning and end of some month.

Also, your Windows REgional settings (Start/Control Panel/Regional and Language
Options/Customize/Date Short Date Format
--ron