Microsoft KB Archive/104705

Microsoft Knowledge Base

Excel: Can't Use Number Command to Reformat Dates in Crosstab

Last reviewed: September 12, 1996
Article ID: Q104705

The information in this article applies to:

• Microsoft Excel for the Macintosh, version 4.0

SYMPTOMS

In Microsoft Excel for the Macintosh, if you use dates as row or column categories in a crosstab table, they will be displayed as text in a "mm/dd/yyyy 00:00:00" format. You may not notice this behavior unless you make the column wide enough to reveal the time portion of the value.

You cannot change this value by choosing the Number command from the Format menu because the information is formatted in a text format.

WORKAROUND

To change the text value back into a date value:

1. In a blank cell, enter the value 1.
2. Copy the cell containing the value 1.
3. Select the cells in the crosstab table that contains the values you want to change.
4. From the Edit menu, choose Paste Special. In the Paste Special dialog box, select the All option under Paste, and select the Multiply option under Operation. Choose OK.

You can now reformat the value by choosing the Number command from the Format menu.

NOTE: This workaround will work only if you do not make changes in the In Groups Of section in the row categories or column categories. If changes are made to the In Groups Of section (for example if you change days, weeks, and so on), the crosstab table will return a text string similar to 02/14/1993 to 02/21/1994, and when you try to multiply by 1 you will receive the #value! in the selected cells.

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. We are researching this problem and will post more information here in the Microsoft Knowledge Base as it becomes available.

KBCategory: kbusage KBSubcategory: Additional reference words: 4.00 report CrosstabWizard ReportWizard Report THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY. Last reviewed: September 12, 1996 ©1997 Microsoft Corporation. All rights reserved. Legal Notices.