Article ID: 109420
Article Last Modified on 8/15/2005
APPLIES TO
- Microsoft Excel 95 Standard Edition
- Microsoft Excel 5.0c
- Microsoft Excel 5.0a for Macintosh
- Microsoft Excel 5.0 Standard Edition
This article was previously published under Q109420
SUMMARY
In the versions of Microsoft Excel listed in the beginning of this article, when you create a PivotTable that uses dates as row or column fields, the dates are converted to text. Because of this conversion, you may have difficulty sorting or otherwise manipulating these dates.
NOTE: This behavior may also occur when you use time values or data formatted with the Zip Code format (in Microsoft Excel version 7.0) as row or column fields. In this case, with data formatted with the Zip Code format in Microsoft Excel version 7.0, any leading zeros in the zip codes will be lost.
MORE INFORMATION
This behavior occurs because Microsoft Excel assumes that PivotTable labels are only used as text labels; therefore, Microsoft Excel converts all the labels to text before placing them in the PivotTable.
If you use dates in the row or column fields, they are converted into text strings that resemble dates. To convert these text versions of dates (or times) back into dates (or times), follow these steps:
- In a blank cell, type 1.
- Copy this cell by clicking Copy on the Edit menu.
- Select the cells that contain the dates that you want to convert.
- On the Edit menu, click Paste Special.
- In the Paste Special dialog box, under Paste, click Values, and under Operation, click Multiply. Click OK. The dates appear as serial numbers (32645, 32647, and so on).
- On the Format menu, click Cells.
- Select the Number tab.
- In the list of Format Codes, click the date format you want.
- Click OK to accept the change.
The text versions of the dates are converted to dates that you can sort and format.
REFERENCES
"User's Guide," version 5.0, pages 470-474
Additional query words: XL5 XL7 XL
Keywords: kbprb KB109420
