Microsoft KB Archive/212152

= XL2000: Lotus 1-2-3 Dates and Phone Numbers Are Converted to One Number =

Article ID: 212152

Article Last Modified on 9/27/2003

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition

-



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:
 * 1) On the Format menu, click Cells. On the Number tab, click Custom.
 * 2) 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



STATUS
Microsoft 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.

