The DATE function is to combine the year, the month and the day to form a date.
Formula:
=DATE(year,month,day)
Explanations:
– Year is required, an integer with one to four digits that stands for the year.
– Month is required, an integer representing January to December.
– Day is required, an integer representing the day from 1 to 31.
Cautions:
Excel for Windows uses the 1900 date system, so the first date is January 1, 1900.
– Year: If between 0 and 1899 (inclusive), you need to add that value to 1900 for the year. For example, DATE(108,1,2) returns January 2, 2008 (1900+108).
| DATE(1,1,1) | 1/1/1901 | 1900 plus 1 equals to 1901 |
| DATE(11, 2, 2) | 2/2/1911 | 1900 plus 11 equals to 1911 |
| DATE(111,2,2) | 2/2/2011 | 1900 plus 111 equals to 2011 |
– Month: If Month is greater than 12, use that value divided by 12, add divisor to the year and remainder for the month.
| DATE(1997, 26, 1) | 2/1/1999 | 26 divided 12 equals 2 and remainder 2 |
| DATE(2005, 13, 1) | 1/1/2006 | 12 divided 12 equals 1 and remainder 1 |
| DATE(2015, 47, 1) | 11/1/2018 | 47 divided 12 equals 3 and remainder 11 |
If Month is less than 0, use December of the year before minus the absolute value of the month.
| DATE(1997, -2, 1) | 10/1/1996 | December the year before is 1996 December, minus 2 months, it is October |
| DATE(2005, -13, 1) | 11/1/2003 | December the year before is 2004 December, minus 13 months, it is November 2003 |
| DATE(2015, -7, 1) | 5/1/2014 | December the year before is 2014 December, minus 7 months, it is May 2014 |
– Day: If Day is greater than the number of days in the month, divided by the number of days, add divisor to the month and use the remainder for the day
| DATE(1997, 4, 41) | 5/11/1997 | 41 divided 30 equals 1 and remainder 11 |
| DATE(2005, 3, 51) | 4/20/2005 | 51 divided 31 equals 1 and remainder 20 |
| DATE(2015, 7, 81) | 9/19/2015 | 81 divided 31 equals 2 and remainder 19 |
If Day is less than 0, use the last day of the Month before minus the absolute value of the days
| DATE(1997, 4, -41) | 2/18/1997 | The last day of before April is March 31, minus 41 days to get Feb 18 |
| DATE(2005, 3, -51) | 1/8/2005 | The last day of before March is Feb 28, minus 51 days to get Jan 8 |
| DATE(2015, 7, -81) | 4/10/2015 | The last day of before July is Jun 30, minus 81 days to get April 10 (to minus 30 days in Jun, 31 days in May and 20 days in Apr) |