DATE

Description: Returns the serial number for a specified date, where the YEAR, MONTH and DAY are separated by commas. For example, =DATE(2011,4,15) returns 40648, which is the serial number that is equivalent to April 15, 2011.

Syntax: DATE(Year, Month, Day)

Year can be one to four digits. The software interprets the Year argument according to the 1900 date system.

If Year is between 0 (zero) and 1899 (inclusive), the software adds that value to 1900 to calculate the year. For example, DATE(108,1,2) returns the serial number representing January 2, 2008 (1900 + 108).

If Year is between 1900 and 9999 (inclusive), the software uses that value as the year. For example, DATE(2011,1,2) the serial number representing January 2, 2011.

Year must be greater than 0 and less than 10000.

Month is a positive or negative integer representing the month of the year from 1 to 12 (January to December).

If Month is greater than 12, Month adds that number of months to the first month in the year specified. For example, DATE(2011,14,2) returns the serial number representing February 2, 2012.

If Month is less than 1, Month subtracts that number of months plus 1 from the first month in the year specified. For example, DATE(2011,-3,2) returns the serial number representing September 2, 2010.

Day is a positive or negative integer representing the day of the month from 1 to 31.

If Day is greater than the number of days in the month specified, Day adds that number of days to the first day in the month. For example, DATE(2011,1,35) returns the serial number representing February 4, 2011.

If Day is less than 1, Day subtracts that number of days plus one from the first day in the month. For example, DATE(2011,1,-15) returns the serial number representing December 16, 2010.

Remarks:

The software stores dates as sequential serial numbers so they can be used in calculations. January 1, 1900 is serial number 1, and January 1, 2011 is serial number 40544 because it is 40,544 days after January 1, 1900.
The DATE function is most useful in formulas where Year, Month, and Day are formulas, not constants.

Example:

DATE(2011, 1, 1) = 1/1/2011 or 40544 [the serial date using the 1900 date system]