XL2000: Custom Date Appears or Date Is Missing in AutoFilter List

• Microsoft Excel 2000

SYMPTOMS

When you are viewing a filtered list in Excel (on the Data menu, point to Filter, and then click to select AutoFilter) you may experience the following problems:

• 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 did not previously create and select a Custom AutoFilter.

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.

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.

MORE INFORMATION

Example

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

2. Select cell A1. On the Data menu, point to Filter, and then click to select AutoFilter.



3. Click the arrow in cell A1.
   
   Note that the list contains the following items:




      (All)
      (Top 10...)
      (Custom...)
      1/1/25
      7/5/98 

4. Click 1/1/25. Note that both the 1/1/1925 and 1/1/2025 dates appear in the filtered list.



5. Click the arrow in cell A1; The (Custom...) item is selected in the list even though you have not previously selected it.



6. Click 7/5/98, and then click the arrow again. Note that the 7/5/98 item is selected in the list.



7. Click (All), and then select cells A2:A4.



8. On the Format menu, click Cells, and then on the Number tab, click Custom in the Category list.



9. In the Type box, type m/d/yyyy, and then click OK.



10. Click the arrow in cell A1.
    
    Note that the dates appear separately in the list. When you select a date, only that date appears in the filtered list.

REFERENCES

For more information about how Microsoft products are affected by year 2000 (Y2K) issues, please see the following Microsoft World Wide Web site:

http://www.microsoft.com/technet/topics/year2k/default.htm

Additional query words: year2000 y2k year 2000 XL2000

Keywords : kb2000 xllist
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbbug