Microsoft KB Archive/106007

= Dates in Unexpected Order in AutoFilter Drop-down List =

Article ID: 106007

Article Last Modified on 8/15/2005

-

APPLIES TO


 * Microsoft Excel 95a
 * Microsoft Excel 95 Standard Edition
 * Microsoft Excel 5.0 Standard Edition
 * Microsoft Excel 5.0c
 * Microsoft Excel 5.0a for Macintosh
 * Microsoft Excel 5.0 for Macintosh

-



This article was previously published under Q106007





SUMMARY
In Microsoft Excel, when you use the AutoFilter command, the order in which dates will appear in the drop-down list depends on how they were originally formatted. Dates will appear in either of the following orders


 * Numerical order (for example, January 1994, February 1994, March 1994, and so on).

-or-
 * Alphabetical order (for example, April 1994, August 1994, December 1994, and so on).

where numerical order means that the dates appear in the list in chronological order and alphabetical order means that any digits in the list items are treated as text for purposes of alphabetization.

Mixing number formats may cause the drop-down list to have no discernible order. For this reason, it is recommended that you use the same number format for all dates in the same column when using the AutoFilter command.



Example
To create an example that will demonstrate how various dates are sorted depending on how they are formatted, do the following:

  In a new worksheet, type the following:       A1:  Date A2: 1/1/94 A3: 2/1/94 A4: 3/1/94 A5: 4/1/94 A6: 5/1/94 A7: 6/1/94 A8: 7/1/94 A9: 8/1/94 A10: 9/1/94 A12: 10/1/94 A13: 11/1/94 A14: 12/1/94  Select any cell in the range A1:A14. On the Data menu, point to Filter, and click AutoFilter.

A drop-down arrow appears in cell A1. When you click this arrow, a list of dates appears: the order in which these dates appear depends on the number format that you apply to cells A2:A14.

The following table contains some of the possible date formats and lists the order in which dates formatted this way appear.   This format   Displays the dates as               In this order ---

m/d/yy       1/1/94, 2/1/94, 3/1/94              Numerical

d-mmm-yy     1-Jan-94, 1-Feb-94, 1-Mar-94        Numerical

d-mmm        1-Jan, 1-Feb, 1-Mar                 Numerical

mmm-yy       Jan-94, Feb-94, Mar-94              Numerical

mmmm         January, February, March            Alphabetical

mmm          Jan, Feb, Mar                       Alphabetical

yyyy mmmm    1994 January, 1994 February         Alphabetical

yyyy-mmmm    1994-January, 1994-February         Alphabetical

yy mmmm      94 January, 94 February,            Alphabetical

yy-mmmm      94-January, 94-February,            Alphabetical

mm           01, 02, 03, 04                      Numerical

m/d/yyyy     1/1/1994, 2/1/1994, 3/1/1994        Numerical

d-mmmm       1-January, 1-February, 1-March      Numerical

mmmm yyyy    January 1994, February 1994         Numerical

d mmmm yyyy  1 January 1994, 1 February 1994     Numerical

mmmm d, yyyy January 1, 1994                     Numerical

mmmm dd, yyyy January 01, 1994                   Numerical

@ (text      1/1/94, 2/1/94, 3/1/94              Numerical   format,   applied to   cells before   dates are   entered)

'date        1/1/94, 2/1/94, 3/1/94              Numerical (apostrophe  entered   before date:   '1/1/94) Dates that are entered as text (for example, Jan, Feb, Mar, and so on), will always appear in alphabetical order.

