Dates in Unexpected Order in AutoFilter Drop-down List

Retired KB Content Disclaimer

View products that this article applies to.

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.

Back to the top

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.

Back to the top

REFERENCES

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

Back to the top

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

Back to the top

KB106007

Back to the top

Retired KB Content Disclaimer

This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.

Back to the top