XL97: Formula Errors Appear After Updating PivotTable

Last reviewed: February 12, 1998
Article ID: Q166740
The information in this article applies to:
  • Microsoft Excel for Windows 95, version 7.0
  • Microsoft Excel 97 for Windows

SYMPTOMS

When you update a PivotTable, formulas that refer to a date in the PivotTable return an error value.

CAUSE

This problem occurs when all of the following conditions are true:

  • You open a workbook that was created in an earlier version of Microsoft Excel.

        -and-
    
  • The workbook includes a PivotTable that contains dates.

        -and-
    
  • You use formulas that explicitly refer to those dates.

For additional information about using dates as text in PivotTables, please see the following article in the Microsoft Knowledge Base:

   Article-ID: Q109420
   TITLE     : XL: Dates in Pivot Table May Be Converted into Text

WORKAROUND

To work around this behavior, edit the formula to refer to the date as a date value instead of a text value. For example, retype the following lookup formula

   =VLOOKUP("7/25/59",$D$1:$G$10,4)

as follows:

   =VLOOKUP(DateValue("7/25/59"),$D$1:$G$10,4)

MORE INFORMATION

In earlier versions of Microsoft Excel, dates in a PivotTable are automatically formatted as text. This means that if you reformat a cell that contains a date with a number format, the format in the cell is reset to a text number format when you update the PivotTable. However, Microsoft Excel 97 allows you to use rich text formatting in a PivotTable, including dates.

Microsoft Excel 97 automatically formats any date in a PivotTable with a date format. This is an enhanced formatting feature and is different from earlier versions of Microsoft Excel. Therefore, a formula that refers to a date that uses the text format in a PivotTable returns an incorrect result or an error value, such as the #N/A or #VALUE! value.


Additional query words: XL97 pivot table break calculation err upgrade
Keywords : xlformula xlpivot
Version : WINDOWS:97
Platform : WINDOWS
Issue type : kbprb
Solution Type : kbworkaround


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: February 12, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.