How to Use DATE Function

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/19011900 plus 1 equals to 1901
DATE(11, 2, 2)2/2/19111900 plus 11 equals to 1911
DATE(111,2,2)2/2/20111900 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/199926 divided 12 equals 2 and remainder 2
DATE(2005, 13, 1)1/1/200612 divided 12 equals 1 and remainder 1
DATE(2015, 47, 1)11/1/201847 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/1996December the year before is 1996 December, minus 2 months, it is October
DATE(2005, -13, 1)11/1/2003December the year before is 2004 December, minus 13 months, it is November 2003
DATE(2015, -7, 1)5/1/2014December 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/199741 divided 30 equals 1 and remainder 11
DATE(2005, 3, 51)4/20/200551 divided 31 equals 1 and remainder 20
DATE(2015, 7, 81)9/19/201581 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/1997The last day of before April is March 31, minus 41 days to get Feb 18
DATE(2005, 3, -51)1/8/2005The last day of before March is Feb 28, minus 51 days to get Jan 8
DATE(2015, 7, -81)4/10/2015The 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)

Download: DATE Function

Leave a Reply