The DATE function, when supplied with integers representing a year, month and day, returns a date.

Syntax

=DATE(year,month,day)

Arguments

Argument Description
year The year to use when creating the date. If year is between zero and 1900, Excel will add 1900 to the year; i.e., if year is entered as 106, Excel adds 1900 to create the year 2006.
month The month to use when creating the date.  If the month parameter is negative or is greater than 12, Excel extends the date back or forward into the previous or following year.
day The day to use when creating the date. If the day parameter is negative or is greater than 31, Excel extends the date back or forward into the previous or following month

Examples

The following spreadsheet shows the Date function applied to different sets of values.

  A B C D E
1 Month Day Year Formula Result
2       =DATE(2001,1,2) 02-Jan-2001
3 5 31 2010 =DATE(C3,A3,B3) 31-May-2010
4 9 18 2012 =DATE(C4,A4,B4) 18-Sep-2012
5 11 10 2014 =DATE(C5,A5,B5) 10-Nov-2014

Usage note:

  • When you supply zero as the day argument to DATE, the date function will roll back one day into the previous month, to the last day of that month; e.g., =DATE(2014,11,0) returns 31-Oct-2014.
  • By adding 1 to the month, and using zero for day, DATE rolls back to the last day of the “original” month; e.g. =DATE(2014,11+1,0) returns 30-Nov-2014.

Common Function Error(s)

Problem What went wrong
Date function looks like a number, e.g. “41061”, instead of a date Likely due to cell formatting, i.e. the function has actually returned the correct value, but the cell is displaying the date serial number, instead of the formatted date