Microsoft Knowledge Base

Excel: DATE() Function Returns #NUM! Error Value
Last reviewed: September 12, 1996
Article ID: Q104952
The information in this article applies to:
  • Microsoft Excel for Windows, versions 4.0, 4.0a, 5.0
  • Microsoft Excel for the Macintosh, version 4.0, 5.0

SUMMARY

The DATE() function in Microsoft Excel will return a #NUM! error value if the Alternate Expression Evaluation option is enabled and if the month argument is greater than twelve or the day argument is greater than the number of days in the specified month.

This limitation of the DATE() function is not documented in the "Function Reference," version 4.0.

MORE INFORMATION

The syntax of the DATE() function is as follows: 

   DATE(year,month,day)
By design, the DATE() function will return an appropriate date when the month argument is greater than twelve and/or the day argument is greater than the number of days in the specified month. For example, DATE(90,14,2) returns February 2, 1991, and DATE(91,1,35) returns February 4, 1991.

However, this feature of the DATE() function is not available when the Alternate Expression Evaluation check box is selected (to select this option, choose the Calculation command from the Options menu). If alternate expression evaluation is enabled, the above examples will return the #NUM! error value.

REFERENCES

"User's Guide 1," page 57 "Function Reference," version 4.0, pages 93-94

KBCategory: kbdocerr
KBSubcategory:

Additional reference words: 4.00 4.00a AEE



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: September 12, 1996
©1997 Microsoft Corporation. All rights reserved. Legal Notices.