XL: Dates May Appear Incorrectly in PivotTable Field Dialog Box

ID: Q176637

The information in this article applies to:

SYMPTOMS

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


In each case, Microsoft Excel converts the date into the last day of the previous month, plus a number of minutes that is equal to the day of the date. For example, 2/9/98 is converted into 1/31/98 (the last day of the previous month) plus 9 minutes (9 from the 9th day), or 1/31/98 0:09.

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.

Additional query words: XL5 XL7 XL97 


Keywords          : xlpivot 
Version           : WINDOWS:5.0,5.0c,7.0,7.0a,97; MACINTOSH:98
Platform          : MACINTOSH WINDOWS 
Issue type        : kbprb

Last Reviewed: April 18, 1999