Microsoft KB Archive/106007
Article ID: 106007
Article Last Modified on 8/15/2005
- 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
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).
- 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.
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.
"User's Guide," version 5.0, Chapter 21
Additional query words: XL5 XL7 XL