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:
- In Microsoft Excel 5.0, select the cell that you want to paste the
date value to.
- 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.
- In the Code box, type mm/dd/yy h:mm AM/PM and choose
OK.
Note that you can use any date format, such as mmmm dd, yyyy, but you MUST type "AM/PM" after the time.
- 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
- In a new project, enter a task, T1.
- 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.
- Select the Finish column for T1, and choose Copy from
the Edit menu.
- 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.
- 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
REFERENCES
For more information about Copying Microsoft Project Text Into Another Application, choose the Search button in Help and type:
copying between applications
Additional query words:
5.00