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

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).

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:

1. 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
   

2. Select any cell in the range A1:A14 and choose Filter from the Data menu, and then choose AutoFilter.

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)

REFERENCES

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