Microsoft KB Archive/180158

= The DATE function does not work as expected in Excel 97 and Excel 98 =

Article ID: 180158

Article Last Modified on 12/2/2004

-

APPLIES TO


 * Microsoft Excel 97 Standard Edition
 * Microsoft Excel 98 for Macintosh

-



This article was previously published under Q180158





SYMPTOMS
When you enter a formula that uses the DATE function, the date returned by the function may not be what you would expect. For example, if you enter this formula =DATE(1899,1,2) the formula returns 1/2/3799 and the expected result is 1/2/1899.



CAUSE
The versions of Microsoft Excel listed at the beginning of this article recognize a larger range of date values than do earlier versions of Excel. Excel treats year parameters that are less than 1900 as offsets of 1900. The difference with Excel 97 and later is that with a year value that is greater than 178 and less than 1900, it results in a year that is greater than the supported range in Excel 95 and earlier, but within the supported date range of Excel 97 and later. These differences are listed in the following table.                                                   Earlier versions Year              Microsoft Excel 97           of Microsoft Excel ---

0 through 178     Adds 1900, producing dates   Adds 1900, producing from 1900 through 2078      dates from 1900 through 2078

179 through 1899  Adds 1900, producing dates   Not accepted; function from 2079 through 3799      returns #NUM! error value

1900 through 2078 Not changed; dates are       Not changed; dates are from 1900 through 2078      from 1900 through 2078

2079 through 9999 Not changed; dates are       Not accepted; function from 2079 through 9999      returns #NUM! error value

10,000 or later   Not accepted; function       Not accepted; function returns #NUM! error value   returns #NUM! error value Note that if a workbook is using the 1904 date system and if the DATE function returns a date from 1900 through 1903, the function returns a #NUM! error value. For more information, please see the following article in the Microsoft Knowledge Base:

175753 XL: DATE Function May Return #NUM! Error When Year Is 0-3



MORE INFORMATION
Earlier versions of Microsoft Excel can handle only dates from 1/1/1900 through 12/31/2078; the versions of Microsoft Excel that are listed at the beginning of this article can handle dates from 1/1/1900 through 12/31/9999.

Because Microsoft Excel does not recognize dates before 1/1/1900, if you enter a formula in which the year argument of the DATE function is less than 1900, Microsoft Excel adds 1900 to the year argument, and then attempts to display the date. The following example illustrates this behavior: =DATE(98,7,5) Because the year argument (98) is less than 1900, Microsoft Excel adds 1900 to the argument (1900 + 98 = 1998) and returns the following date:

7/5/1998

Because earlier versions of Microsoft Excel cannot handle dates greater than 12/31/2078, if the value of the year argument becomes larger than 2078, the DATE function returns a #NUM! error value. This is normal behavior for earlier versions of Microsoft Excel.

However, because Microsoft Excel 97 can handle dates through the year 9999, you do not receive a #NUM! error value unless the value of the year argument is greater than 9999.

Additional query words: XL97 y2k year2000 1900 1901 1902 1903 XL

Keywords: kbbug kbpending KB180158

-

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

© Microsoft Corporation. All rights reserved.