Microsoft KB Archive/109420

= Dates in Pivot Table May Be Converted into Text =

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:
 * 1) In a blank cell, type 1.
 * 2) Copy this cell by clicking Copy on the Edit menu.
 * 3) Select the cells that contain the dates that you want to convert.
 * 4) On the Edit menu, click Paste Special.
 * 5) 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).


 * 1) On the Format menu, click Cells.
 * 2) Select the Number tab.
 * 3) In the list of Format Codes, click the date format you want.
 * 4) Click OK to accept the change.

The text versions of the dates are converted to dates that you can sort and format.

