Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. XL2000: DATE Function Behaves Differently in Microsoft Excel 2000

XL2000: DATE Function Behaves Differently in Microsoft Excel 2000

View products that this article applies to.

This article was previously published under Q214331

↑ Back to the top

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(10,1,2)
the formula returns 1/2/1910 and the expected result is 1/2/2010.

Additionally, if the DATE function uses a year that is earlier than 1900, Excel adds 1900 to the year argument. For example, if you enter this formula 
   =DATE(1899,1,2)
the formula returns 1/2/3799.

↑ Back to the top

Cause

Microsoft Excel 97 and later 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. 
                      Microsoft Excel 97           Earlier versions
   Year               Microsoft Excel 2000         of Microsoft Excel
   -----------------------------------------------------------------------

   0 through 178      Not changed; adds 1900,      Not changed; adds 1900,
                      producing dates from         producing dates from
                      1900 through 2078            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:
214365� XL2000: DATE Function May Return #NUM! Error When Year Is 0-3

↑ Back to the top

More information

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. 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 versions of Microsoft Excel earlier than Excel 97 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 and later 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.

↑ Back to the top

Keywords: KB214331, kbpending, kbbug

↑ Back to the top

Article Info
Article ID : 214331
Revision : 1
Created on : 11/5/2003
Published on : 11/5/2003
Exists online : False
Views : 273