Syntax. DATE(year,month,day)
Definition. This function returns the serial number for a date indicated by the year, month, and day arguments.
Arguments
year (required). The year, which can consist of one to four digits. Four digits is recommended.
month (required). A number for the month of the year.
day (required). A number for the day of the month.
Background. When calculating dates, you sometimes need to work with the component parts and might then want to return the value as a normal date. The DATE() function converts the parts of a date back into a (numeric) date expression that you can use in other date calculations. Excel interprets the year argument according to the date system being used by your computer. Excel for Windows uses the 1900 date system, and Excel for Mac uses the 1904 date system.
The following applies to the 1900 date system:
For a year from 0 (zero) through 1899, Excel adds the value to 1900 to calculate the year.
For a year from 1900 through 9999, Excel uses this value as year.
If the year is less than 0 or greater than or equal to 10000, Excel returns the #NUMBER! error.
The following applies to the 1904 date system:
For a year from 4 through 1899, Excel adds the value to 1900 to calculate the year.
For a year from 1904 through 9999, Excel uses this value as the year.
If the year is less than 4 or greater than or equal to 10000, or for a year from 1900 through 1903, Excel returns the #NUMBER! error.
For both systems, if the month is greater than 12, the number of months is added to the first month of the given year, and if the day is greater than the number of days in the given month, this number is added to the first day of the month.
For more information about saving dates in Excel, see the section titled The Excel Date System in Chapter 2.
Example. You might need to calculate the first and the last day of the month for a given date in an accounting application. The formula
=DATE(YEAR("07/23/2008"),MONTH("07/23/2008"),1)returns 07/01/2008 as the first day of the month. The formula generates the number of the year and month from the given date with the day number 1. The formula
=DATE(YEAR("07/23/2008"),MONTH("07/23/2008")+1,1)-1returns 07/31/2008 as the last day of the month. The formula generates the number of the year and the month increased by 1 (the following month) from the given date with the day number 1. If you subtract one day, you get the last day of the previous month.
=DATE(108,1,2) returns 01/02/2008 (1900+108).
=DATE(2008,1,2) returns 01/02/2008.
=DATE(2008,14,2) returns 02/02/2009.
=DATE(2008,1,35) returns 02/04/2008.