XL: Dates in Unexpected Order in AutoFilter Drop-down List

ID: Q106007

The information in this article applies to:

Microsoft Excel for Windows, versions 5.0, 5.0c
Microsoft Excel for the Macintosh, versions 5.0, 5.0a
Microsoft Excel for Windows 95, versions 7.0, 7.0a

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.

MORE INFORMATION

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.

REFERENCES

"User's Guide," version 5.0, Chapter 21

Additional query words: XL5 XL7


Keywords          : 
Version           : 5.00 5.00c 7.00 7.00a | 5.00 5.0
Platform          : WINDOWS 
Issue type        :

Last Reviewed: July 29, 1999