XL: DATE Function Behaves Differently in Microsoft Excel 97

• Microsoft Excel 97 for Windows
• Microsoft Excel 98 Macintosh Edition

SYMPTOMS

In the versions of Microsoft Excel that are listed at the beginning of this article, when you enter a formula that uses the DATE function, the date returned by the function may be incorrect. For example, if you enter this formula

   =DATE(1899,1,2)

CAUSE

The versions of Microsoft Excel listed at the beginning of this article handle the year argument of the DATE function differently than earlier versions of Microsoft Excel do. 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

   ARTICLE-ID: Q175753
   TITLE     : XL: DATE Function May Return #NUM! Error When Year Is 0-3

STATUS

This behavior is by design of the versions of Microsoft Excel that are listed at the beginning of this article.

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. For example, consider the following formula:

   =DATE(98,7,5)

   7/5/1998

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.