XL97: Formula Errors Appear After Updating PivotTable

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.

The workbook includes a PivotTable that contains dates.

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:

Q109420 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 :
Version : WINDOWS:97; Win95:7.0
Platform : Win95 WINDOWS
Issue type :