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 INFORMATIONExample
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 :
|