XL2000: Lotus 1-2-3 Dates and Phone Numbers Are Converted to One Number (212152)
The information in this article applies to:
This article was previously published under Q212152 SYMPTOMS
When you open a file that was created in Lotus 1-2-3, cells that contain a date or
phone number in Lotus 1-2-3 display a single number in Microsoft Excel 2000. For
example, a date that appears as 05-01-97 in Lotus 1-2-3 appears as -93 in Excel. Also, a phone number that appears as 555-1212 in Lotus 1-2-3, appears as -657 in Excel.
CAUSE
This problem can occur when both of the following conditions are true:
- In the Lotus 1-2-3 worksheet, you format a cell with a Text number
format.
-and-
- You type a date or phone number that is separated by hyphens, for
example, 4-31-97 or 555-1212.
WORKAROUND
To return the correct date, use one of the following methods.
Method 1: Use a Date Number Format in Lotus 1-2-3
Before you save the Lotus 1-2-3 file, format any dates that use a Text
number format with a Date number format. For dates that are separated with
a hyphen (-) character, replace the hyphen with a slash mark (/).
NOTE: The Lotus 1-2-3 Date number format does not recognize any date
separator except for the slash mark.
Method 2: Remove the Equal Sign Before the Number in Microsoft Excel
Check the formula bar to see how the date or phone number should appear.
For example, if cell A1 contains the formula =4-31-97, the date in Lotus is
4-31-97. In Microsoft Excel, click the formula bar and remove the equal
sign before the date or phone number. The date should appear
correctly.
If the date does not appear with hyphen separators, you can create a custom
number format in Microsoft Excel to display the date with the hyphen. To
create a custom number format to display a date with a hyphen separator,
follow these steps:
- On the Format menu, click Cells. On the Number tab, click Custom.
- In the box below Type, type the date format code.
For example, if you want to display April 31, 1997 as 4-31-97, type the
following:
m-dd-yy
STATUSMicrosoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article. MORE INFORMATION
Microsoft Excel can import and interpret a date from Lotus 1-2-3 if the
date is generated by a date function or a built-in number format. Excel
does not interpret the Lotus 1-2-3 Text number format. Instead, Excel
converts the Text format to the General number format. Any dates or phone
numbers that are displayed as text in Lotus 1-2-3 are converted to formulas
in Excel. For example, a date of 5-01-97 is converted to =5-01-97, or the
value -93.
REFERENCESFor more information about formatting issues when you import files fromLotus 1-2-3, click Microsoft Excel Help on the Help menu, type troubleshoot converting file formats in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
| Modification Type: | Major | Last Reviewed: | 9/27/2003 |
|---|
| Keywords: | kbbug kbpending kbusage KB212152 |
|---|
|