XL: Dates in Pivot Table May Be Converted into Text

• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for the Macintosh, versions 5.0, 5.0a
• Microsoft Excel for Windows NT, version 5.0
• Microsoft Excel for Windows 95, version 7.0

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" (without the quotation marks).

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).

6. On the Format menu, click Cells.

7. Select the Number tab.

8. In the list of Format Codes, click the date format you want.

9. Click OK to accept the change.

REFERENCES

"User's Guide," version 5.0, pages 470-474