Select the product you need help with
- Internet Explorer
- Windows Phone
- More products
OFF2000: Dates Prior to 1900 Allowed in Office Spreadsheet
Article ID: 210782 - View products that this article applies to.
This article was previously published under Q210782
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 1900In 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/1899Entering this date in Excel will evaluate to a text string.
Entering a Large NumberIn 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 DateIf 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 (########).
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:
(http://support.microsoft.com/kb/216578/EN-US/ )XL2000: Calculation Differences with the Office Spreadsheet
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.
Article ID: 210782 - Last Review: July 27, 2001 - Revision: 1.0