Microsoft KB Archive/113767

{|
 * width="100%"|

PRJ4: Date Value Pasted to Microsoft Excel in Incorrect Format

 * }

Q113767

-

The information in this article applies to:


 * Microsoft Project for Windows, version 4.0

-

SYMPTOMS
If you copy a date value in Microsoft Project, and you paste the date to a cell in Microsoft Excel, the date value is displayed as a serial number, such as 34365.71.

If you paste the date as Text to a cell in Microsoft Excel, the date value is displayed in the 24-hour time format, even if it is displayed in the 12-hour format in Microsoft Project.

CAUSE
When you copy a date value in Microsoft Project, and choose Paste Special in Microsoft Excel, the following formats are available from the As list in the Paste Special dialog box:

Microsoft Project 4.0

Picture

BIFF4

BIFF3

BIFF

Text The default format, BIFF4, is used when you choose Paste from the Edit menu. When you use this format to paste your date value from Microsoft Project, the pasted date value appears as a serial number.

When you paste a date value from Microsoft Project as Text using the Paste Special dialog box, the date value appears as a date. However, when the date value you copy from Microsoft Project contains AM or PM, the pasted date value appears in the 24-hour format. For example, if you copy the date value "Jan 31, 5:00 PM" in Microsoft Project, the value is pasted as "1/31/94 17:00" in Microsoft Excel.

This problem occurs because Microsoft Excel detects the value from Microsoft Project as a date, and automatically formats the date in the format m/d/yy h:mm (the closest format to the Microsoft Project format).

WORKAROUND
To display a date value that you copy from Microsoft Project in the 12-hour number format in Microsoft Excel, do the following:


 * 1) In Microsoft Excel 5.0, select the cell that you want to paste the date value to.
 * 2) In Microsoft Excel 5.0, choose Cell from the Format menu and select the Number tab. In Microsoft Excel version 4.0, choose Number from the Format menu.
 * 3) In the Code box, type mm/dd/yy h:mm AM/PM and choose OK.
 * 4) Paste the date value from Microsoft Project as Text in the cell you just formatted.

Also note that you can format the cell either before or after you paste the date value.

Steps to Reproduce Behavior

 * 1) In a new project, enter a task, T1.
 * 2) From the Tools menu, choose Options, and select the View tab. From the Date Format list, select Jan 31 12:33 PM, and choose OK.
 * 3) Select the Finish column for T1, and choose Copy from the Edit menu.
 * 4) Start Microsoft Excel version 5.0. Select cell A1, and choose Paste Special from the Edit menu. From the As list, select Text, and choose OK.
 * 5) If the cell A1 contains ######, select A1, choose Column from the Format menu and then choose AutoFit Selection.

The date you copied from Microsoft Project is displayed in the 24 hour number format.

For more information about pasting date values in Microsoft Excel, query on the following words in the Microsoft Knowledge Base:

"text and values and excel and dates"