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

This article was previously published under Q175753
This article has been archived. It is offered "as is" and will no longer be updated.
SYMPTOMS
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 Excelfor the Macintosh, this problem is more likely to occur on the Macintosh.This behavior is by design of Microsoft Excel.
WORKAROUND
To work around this problem, use a full four-digit year argument that isgreater than or equal to 1904. For example, instead of the followingformula
=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.
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:
  1. On the Tools menu, click Options or Preferences. Click the Calculation tab.
  2. Click to select the 1904 Date System check box to use that date system; to use the 1900 Date System, clear the check box.
  3. Click OK.
When you use the DATE function to return the serial number of a particulardate, and you use a one- or two-digit year argument, the function assumesthat the date is in the 20th century (19xx). Because the 1904 Date Systemdoes not support dates before January 1, 1904, the DATE function fails ifyou specify a year argument that is less than 1904. For example, thefollowing 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				
XL5 XL7 XL97 5.00a 5.00c 7.00a year2000 y2k XL
Properties

Article ID: 175753 - Last Review: 12/05/2015 08:08:22 - Revision: 1.3

Microsoft Excel 2000 Standard Edition, Microsoft Excel 97 Standard Edition, Microsoft Excel 95 Standard Edition, Microsoft Excel 5.0 Standard Edition, Microsoft Excel 98 for Macintosh

  • kbnosurvey kbarchive kbprb KB175753
Feedback