Description
Used to return a valid date from the year, month, and day component directly punched in function or referred from any cell. DATE function is particularly useful when used as input for other nested functions (e.g PERIOD FUNCTIONS) and when dynamic input in Excel modeling is required.
Remember not to confuse DATE with DAY function (in which Excel return the serial number of a date of a month) OR EDATE function ( in which Excel the date after a given number of months.
=DATE(year,month,day)
The DATE function syntax has the following argument:
- Year Required.
The year argument can be inserted in one to four digits. By default, Microsoft Excel for Windows assumes the first date is January 1, 1900.
If the year is between 0 (zero) and 1899, Excel adds that value to 1900 to calculate the year. For example, DATE(217,3,15) returns March 15, 2117(1900+217).
In order to avoid error while using the DATE function, use four digits for the year argument. For example, “17” could mean “1917” or “2017”. The use of Four-digit years in the Excel DATE function will prevent such confusion.
If the year is less than 0 or is 10000 or greater, Excel returns the #NUM! error value.
- Month Required.
If the referred month is greater than 12, Excel adds that excess number (exceeding 12) to the first month in the next year. For example, DATE(2010,16,2) returns the date April 02, 2011.
If the month is less than 1, Excel subtracts -ve number, from the first month in the year specified. For example, DATE(2010,-16,2) returns the serial number representing August 2, 2008.
- Day Required.
If the day is greater than the number of days in the month specified, the day adds that number of days to the first day in the month. For example, DATE(2010,3,35) returns the serial number representing April 4, 2010.
If the day is less than 1, Excel subtracts the magnitude of that the number of days, plus one, from the first day of the month specified. For example, DATE(2008,1,-15) returns the serial number representing December 16, 2007.
