XL97: Lotus 1-2-3 Dates Are Converted to Number

ID: Q168102

The information in this article applies to:
  • Microsoft Excel 97 for Windows
  • Microsoft Excel for Windows 95, version 7.0

SYMPTOMS

When you open a file created in Lotus 1-2-3, cells that contain a date or phone number in Lotus 1-2-3 display a number in Microsoft Excel 97. For example, a date that appears as 05-01-97 in Lotus 1-2-3, appears as -93. Also, a phone number that appears as 555-1212 in Lotus 1-2-3, appears as -657.


CAUSE

This problem occurs 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 dashes, 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 dash (-) character, replace the dash with a slash (/) character.

NOTE: The Lotus 1-2-3 Date number format does not recognize any date separator except for the slash character.

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 phone number should appear correctly.

If the date does not appear with dash separators, you can create a custom number format in Microsoft Excel to display the date with the dash. To create a custom number format to display a date with a dash separator, follow these steps:

  1. On the Format menu, click Cells. In the Number tab, click the Custom category.


  2. In the box below Type, enter the date format code.

    For example, if you want to display April 31, 1997 as 4-31-97, enter the following:
    m-dd-yy



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.


REFERENCES

For more information about formatting issues when you import files from Lotus 1-2-3, click the Index tab in Excel 97 Help, type the following text

Lotus, converting files from
and then double-click the selected text to go to the "Formatting and features not transferred in file conversions" topic.

Additional query words: XL97 123

Keywords : xlformat xl123Quattro
Version : WINDOWS:97; Win95:7.0
Platform : Win95 WINDOWS
Issue type : kbprb


Last Reviewed: November 4, 1999
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.