OFF2000: Dates Prior to 1900 Allowed in Office Spreadsheet

ID: Q210782

The information in this article applies to:

Microsoft Office 2000
Microsoft Office Spreadsheet Component 9.0, run time
Microsoft Excel 2000

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:

Q216578 XL2000: Calculation Differences with the Office Spreadsheet

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article.

Additional query words: OFF2000 owc


Keywords          : kbdta kbweb xlformat 
Version           : WINDOWS:2000
Platform          : WINDOWS 
Issue type        : kbbug

Last Reviewed: June 7, 1999