XL97: Formula Errors Appear After Updating PivotTable (166740)
The information in this article applies to:
- Microsoft Excel for Windows 95
- Microsoft Excel 97 for Windows
This article was previously published under Q166740 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:
109420 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.
| Modification Type: | Minor | Last Reviewed: | 8/17/2005 |
|---|
| Keywords: | KB166740 |
|---|
|