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

Last reviewed: February 5, 1998
Article ID: Q175753

The information in this article applies to:

Microsoft Excel for Windows, versions 5.0, 5.0c
Microsoft Excel for the Macintosh, versions 5.0, 5.0a
Microsoft Excel for Windows NT, version 5.0
Microsoft Excel for Windows 95, versions 7.0, 7.0a
Microsoft Excel 97 for Windows
Microsoft Excel 98 Macintosh Edition

SYMPTOMS

In the versions of Microsoft Excel listed at the beginning of this article, the DATE function may return a #NUM! error even though the year, month, and day arguments are all valid. For example, the following formula

   =DATE(1,7,5)

may return a #NUM! error.

CAUSE

This problem occurs when the following conditions are true:

The workbook in which you are using the function uses the 1904 Date System.
```
    -and-
```

The year argument is one of the following values:

      0   00   1900
      1   01   1901
      2   02   1902
      3   03   1903

Because the 1904 Date System is the default date system in Microsoft Excel for the Macintosh, this problem is likely to occur on the Macintosh.

WORKAROUND

To work around this problem, use a full four-digit year argument that is greater than or equal to 1904. For example, instead of the following formula

   =DATE(1,7,5)

use the following formula:

   =DATE(2001,7,5)

By specifying a valid full four-digit year, you can prevent the DATE function from returning a #NUM! error value.

STATUS

This behavior is by design of Microsoft Excel.

MORE INFORMATION

In Microsoft Excel, you can use either of following two date systems.

   Date system        First day is      Default date system in
   ----------------------------------------------------------------------

   1900 Date System   January 1, 1900   Microsoft Excel for Windows
                                        Microsoft Excel for Windows NT

   1904 Date System   January 1, 1904   Microsoft Excel for the Macintosh

To change the date system for the active workbook, follow these steps:

On the Tools menu, click Options or Preferences. Click the Calculation tab.
Click to select the 1904 Date System check box to use that date system; to use the 1900 Date System, clear the check box.
Click OK.

When you use the DATE function to return the serial number of a particular date, and you use a one- or two-digit year argument, the function assumes that the date is in the 20th century (19xx). Because the 1904 Date System does not support dates before January 1, 1904, the DATE function fails if you specify a year argument that is less than 1904. For example, the following results are displayed.

                      Result when using   Result when using
   Formula            1900 Date System    1904 Date System
   ----------------------------------------------------------------

   =DATE(0,1,1)       1/1/1900            #NUM!

   =DATE(1,7,5)       7/5/1901            #NUM!

   =DATE(1902,8,12)   8/12/1902           #NUM! (since year < 1904)

   =DATE(4,3,31)      3/31/1904           3/31/1904

Additional query words: XL5 XL7 XL97 5.00a 5.00c 7.00a year2000 y2k
Keywords : xlformula
Version : MACINTOSH:5.0,5.0a,98; WINDOWS:5.0,5.0c,7.0,7.0a,97
Platform : MACINTOSH WINDOWS
Issue type : kbprb

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: February 5, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.