Select the product you need help with

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

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 (########).

Back to the top | Give Feedback

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
(http://support.microsoft.com/kb/216578/EN-US/ )
XL2000: Calculation Differences with the Office Spreadsheet

Back to the top | Give Feedback

STATUS

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

Back to the top | Give Feedback

Properties

Article ID: 210782 - Last Review: July 27, 2001 - Revision: 1.0

APPLIES TO

Microsoft Office Spreadsheet Component 9.0, run time

kbbug kbpending kbweb KB210782

Retired KB Content Disclaimer

This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.

Back to the top | Give Feedback