XL: Dates May Appear Incorrectly in PivotTable Field Dialog Box

• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for Windows 95, versions 7.0, 7.0a
• Microsoft Excel 97 for Windows
• Microsoft Excel 98 Macintosh Edition

SYMPTOMS

In the versions of Microsoft Excel listed at the beginning of this article, if you click a field in a PivotTable, and then click the PivotTable Field tool on the PivotTable toolbar or on the Query And Pivot toolbar, dates in the Hide Items list may appear incorrectly. For example, sample dates that may appear are listed in the following table.

   Dates that      Dates that
   should appear   actually appear
   -------------------------------

   1/5/98          12/31/97 0:05
   1/12/98         12/31/97 0:12
   1/19/98         12/31/97 0:19
   1/26/98         12/31/97 0:26
   2/2/98           1/31/98 0:02
   2/9/98           1/31/98 0:09
   2/16/98          1/31/98 0:16
   2/23/98          1/31/98 0:23
   3/2/98           2/28/98 0:02

If you select any items in the list and click OK, the dates in the PivotTable are converted into the items that appeared in the list.

CAUSE

This problem occurs if you create the PivotTable in Microsoft Excel for the Macintosh, version 5.0 or 5.0a.

NOTE: This problem does NOT occur if you create the PivotTable in Microsoft Excel for Windows, version 5.0, Microsoft Excel for Windows, version 7.0, Microsoft Excel 97 for Windows, or Microsoft Excel 98 Macintosh Edition.

The problem does not occur if you click the PivotTable Field tool in Microsoft Excel for the Macintosh, version 5.0 or 5.0a.

WORKAROUND

To work around this problem, delete the existing PivotTable, and then recreate it in a version of Microsoft Excel other than Microsoft Excel for the Macintosh, version 5.0 or 5.0a.

STATUS

Microsoft has confirmed this to be a problem in Microsoft Excel for the Macintosh, version 5.0 and 5.0a. This problem was corrected in later versions of Microsoft Excel.

MORE INFORMATION

In Microsoft Excel, you can use the PivotTable Field tool to configure a field in a PivotTable. You can change the name of the field, change its orientation, customize subtotals, and hide specific items in the field.