Microsoft KB Archive/183815

= Custom Date Appears or Date Is Missing in AutoFilter List =

Article ID: 183815

Article Last Modified on 12/30/2005

-

APPLIES TO


 * Microsoft Excel 97 Standard Edition
 * Microsoft Excel 95 Standard Edition
 * Microsoft Excel 5.0 Standard Edition
 * Microsoft Excel 5.0c
 * Microsoft Excel 95a
 * Microsoft Excel 5.0 Standard Edition
 * Microsoft Excel 5.0 for Macintosh
 * Microsoft Excel 5.0a for Macintosh
 * Microsoft Excel 5.0 for Macintosh
 * Microsoft Excel 5.0a for Macintosh

-



This article was previously published under Q183815



SYMPTOMS
In the versions of Microsoft Excel listed at the beginning of this article, the following problems may occur when you click an AutoFilter arrow to display an AutoFilter list:


 * The AutoFilter list contains one entry that represents two different dates. When you select the entry, both dates appear in the filtered list. -and-


 * When you click an item in the AutoFilter list, the selected item may be "(Custom...)" even though you have not previously selected the Custom item.



CAUSE
These problems may occur when the following conditions are true:


 * You are using the AutoFilter to filter a list that contains dates. -and-


 * The dates have been formatted to use only two digits for years. For example, January 1, 1925 appears as "1/1/25". -and-


 * The list contains dates in more than one century: for example, dates in the 20th and 21st centuries.

The problem occurs because the items in the AutoFilter list are displayed with the format that is applied to the cells in the list. If two dates are formatted so that they appear to be the same date, only one entry appears in the AutoFilter list for the dates.

NOTE: The problem in which "(Custom...)" is selected does not occur in Microsoft Excel versions 5.0 and 7.0.

For an example of this problem, please see the "More Information" section.



WORKAROUND
To work around this problem, format the dates in your list so that they use four digits for years. If the dates appear differently, each date appears separately in the AutoFilter list when you click it.



STATUS
Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article.



Example
To see an example of the problem that is described in this article, follow these steps:

  In Microsoft Excel, create a new workbook. In Sheet1, enter the following values and formulas:      A1: Date       B1: Year A2: 1/1/1925  B2: =YEAR(A2) A3: 1/1/2025  B3: =YEAR(A3) A4: 7/5/1998  B4: =YEAR(A4)  Select cell A1. On the Data menu, point to Filter, and then click AutoFilter. Click the arrow in cell A1.

Note that the list contains the following items:

(All)

(Top 10...)

(Custom...)

1/1/25

7/5/98

 Select 1/1/25.

Note that both the 1/1/1925 and 1/1/2025 dates appear in the filtered list. Click the arrow in cell A1.

If you are using Microsoft Excel 97, note that the "(Custom...)" item is selected in the list even though you have not previously selected it.</li> Click 7/5/98. Then, click the arrow again.

Note that the 7/5/98 item is selected in the list.</li> Click "(All)".</li> Select cells A2:A4. On the Format menu, click Cells. Click the Number tab. In the Category list, click Custom. In the Type box, type m/d/yyyy. Click OK.</li> Click the arrow in cell A1.</li></ol>

The dates each appear separately in the list. When you select a date, only that date appears in the filtered list.

Additional query words: XL97 XL5 XL7 5.0 7.0 year2000 y2k year 2000 XL

Keywords: kbprb KB183815

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.