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

sunny · External Since: Jul 20, 2006 Posts: 5

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.

Gord Dibben · External Since: Feb 23, 2004 Posts: 9178

Sounds like some dates are text, not real dates.

Select your column of dates and Data>Text to Columns>Next>Next>Column Data
Format>Date>MDY and Finish.

Gord Dibben MS Excel MVP

On Tue, 15 Sep 2009 12:43:01 -0700, sunny <sunny.RemoveThis@discussions.microsoft.com>
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.

Niek Otten · External Since: Feb 23, 2004 Posts: 1957

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" <sunny RemoveThis @discussions.microsoft.com> wrote in message
news:76C549AD-615A-49C5-9DAB-30B3A198CBBF@microsoft.com...
> 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.