Sorting problem

[Pam Graham]

I'm trying to sort a list by year.
When I sort, the results sort in two parts -The first part
is sorted from 1938 to 2003 and the next is sorted 1930-2003.

How do I get the listed to sort properly?

 

[Norman Harker]

Hi Pam!

I suspect that it is sorting correctly but there might be problems
with the Regional Options setting for the double digit year
interpretation.

Before or after sorting, check your dates are what you think they are
by formatting (e.g.) dd-mmm-yyyy

If they are correct and you still have problems report back. If they
are not correct then there may be ways of correcting with formulas
after establishing the default double digit year interpretation.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Pam]

Hi Pam,

Can it be that some of your dates are formated as text?
If this is not the problem, please post back.

Greg

 

[David McRitchie]

Hi Pam,
If you try Norman's suggestion to format with 4-digit years you will
have a better ideaof what you actually have. But those breakdowns for
dates look something like what you get when you enter 2 digit years
when entering dates. As the versions of Excel move up so
do the default date ranges implied for two digit years. It is best to
always show four digit years at all times.

214391 - XL2000: How Microsoft Excel Works with Two-Digit Year Numbers
http://support.microsoft.com/default.aspx?scid=kb;en-us;q214391

If you are running Microsoft Windows 98 or later, you can use the When a two
digit year is entered, interpret a year between setting under Regional Settings
in Control Panel to determine the cutoff year for the century. The default value
is 2029, but you can change this to any value between 99 and 9999.

Information on Date and Time can be seen on my Date and Time page.
http://www.mvps.org/dmcritchie/excel/datetime.htm

BTW, To determine if you have Text or numbers
Determine if cell is Number or Text and why is it seen that way (#debugformat)
http://www.mvps.org/dmcritchie/excel/join.htm#debugformat
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm