Excel 2003 seems to sort column of date, some by month & some by y

Hi,

I inherited an Access 2003 database where I reformatted all the date to
mm/dd/yyyy. I then copied it to Excel 2003 and sorted by date. For some
strange reason, I ended up with 2 sections, 1 sorted by the mm and the other
by the yyyy. I reformatted the column by date but it still has the same
problem.

Can anyone please help me with this? Thank you.

Probably the section sorted on mm was not recognized as dates and sorted as
text. Maybe your input was in dd/mm/yyyy format, on a machine wuth US
settings. Then 13/01/2001, meaning Jan 13 2001, but will not be recognized
as date because of the "month" 13.

You can find out with the ISTEXT() function.


--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"sunny" wrote in message
...
Hi,

I inherited an Access 2003 database where I reformatted all the date to
mm/dd/yyyy. I then copied it to Excel 2003 and sorted by date. For some
strange reason, I ended up with 2 sections, 1 sorted by the mm and the
other
by the yyyy. I reformatted the column by date but it still has the same
problem.

Can anyone please help me with this? Thank you.

Sounds like some dates are text, not real dates.

Select your column of dates and DataText to ColumnsNextNextColumn Data
FormatDateMDY and Finish.


Gord Dibben MS Excel MVP

On Tue, 15 Sep 2009 12:43:01 -0700, sunny
wrote:

Hi,

I inherited an Access 2003 database where I reformatted all the date to
mm/dd/yyyy. I then copied it to Excel 2003 and sorted by date. For some
strange reason, I ended up with 2 sections, 1 sorted by the mm and the other
by the yyyy. I reformatted the column by date but it still has the same
problem.

Can anyone please help me with this? Thank you.