Microsoft KB Archive/210782

= OFF2000: Dates Prior to 1900 Allowed in Office Spreadsheet =

Article ID: 210782

Article Last Modified on 7/27/2001

-

APPLIES TO


 * Microsoft Office Spreadsheet Component 9.0, run time

-



This article was previously published under Q210782



SYMPTOMS
In the Microsoft Office Spreadsheet Component, the following behavior occurs with date values evaluating to a date prior to the year 1900. This is different than Excel which only allows years in the range from 1900 to 9999.

Dates Prior to 1900
In the Office Spreadsheet Component, you can enter a date whose year is earlier than the year 1900.

For example, the following date is valid in the Office Spreadsheet Component, but not valid in Excel:

1/1/1899

Entering this date in Excel will evaluate to a text string.

Entering a Large Number
In the Office Spreadsheet Component, entering a time with hours larger than 32767 evaluates to a date before the year 1900, and the underlying value is a negative number.

For example, if you type the number 32768:00, the date 4/4/1896 8:00am is displayed. The number that appears in the Formula in active cell box of the Spreadsheet Property Toolbox dialog box, is -1365.33333333333.

Negative Number Evaluates to Date
If you enter a negative number into a cell, you can format the cell with a date format and a date before the year 1900 appears. For example, if you enter the number -123456 and format the cell with a date format, 12/26/1561 will appear. In the Office Spreadsheet Component, when you click Export to Excel or copy the date to Excel, the date will appear in the Excel workbook as a series of pound signs (########).



WORKAROUND
To correct this problem, change the number format from a Date format to another format, such as General. Excel does not recognize dates prior to the year 1900 and therefore cannot format a cell with a date number format.

For more information about differences between the Office Spreadsheet Component and Excel, please see the following article in the Microsoft Knowledge Base:

216578 XL2000: Calculation Differences with the Office Spreadsheet



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

Additional query words: OFF2000 owc

Keywords: kbbug kbweb kbpending KB210782

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.