Excel DATE Function

Related Functions:

TODAY
DATEVALUE

Basic Description

The Excel Date function converts a supplied year, month and day into an Excel date.

The syntax of the function is:

DATE( year, month, day )

where the year, month and day arguments are integers representing the year, month and day of the required date.


Date Function Examples

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

 Formulas:
 ABCD
1DayMonthYearDate
2   =DATE( 2001, 1, 2 )
33151998=DATE( C3, B3, A3 )
42151984=DATE( C4, B4, A4 )
5912012=DATE( C5, B5, A5 )
 Results:
 ABCD
1DayMonthYearDate
2   02-Jan-2001
3315199831-May-1998
4215198421-May-1984
591201209-Jan-2012

Date Function Arguments

Typically, when using the Date function, the supplied year will be between 1900 and 9999, the month will be between 1 and 12 and the day will be between 1 and 31. However, these values can extend outside these ranges, in which case, they behave as follows:

year-

If the supplied year argument is between 0 and 1899, this value is added onto 1900. For example:

DATE( 1, 1, 1 )=1st January 1901
DATE( 112, 1, 1 )=1st January 2012
month-

If the supplied month argument is negative or is greater than 12, the date extends back or forward, into the previous or following year. For example:

DATE( 2012, -1, 1 )=1st November 2011
DATE( 2012, 0, 1 )=1st December 2011
DATE( 2012, 1, 1 )=1st January 2012
DATE( 2012, 2, 1 )=1st February 2012
.
.
.
.
.
.
DATE( 2012, 12, 1 )=1st December 2012
DATE( 2012, 13, 1 )=1st January 2013
DATE( 2012, 14, 1 )=1st February 2013
day-

If the supplied day argument is negative or is greater than 31, the date extends back or forward, into the previous or following month. For example:

DATE( 2012, 6, -1 )=30th May 2012
DATE( 2012, 6, 0 )=31st May 2012
DATE( 2012, 6, 1 )=1st June 2012
DATE( 2012, 6, 2 )=2nd June 2012
.
.
.
.
.
.
DATE( 2012, 6, 30 )=30th June 2012
DATE( 2012, 6, 31 )=1st July 2012
DATE( 2012, 6, 32 )=2nd July 2012


Further information and examples of the Excel Date function are provided on the Microsoft Office website.


Date Function Errors

If you get an error from the Excel Date Function, this is likely to be one of the following:

Common Errors
#NUM!-Occurs if the supplied year argument is < 0 or ≥ 10000.
#VALUE!-Occurs if any of the supplied arguments are non-numeric.

Also, some users of the Excel Date function encounter the following problem:

Common Problem:

The result of your Date function looks like a number (e.g. "41061"), instead of a date.

Solution:

This is likely to be due to the formatting of the cell. I.e. the function has actually returned the correct value, but the cell is displaying the date serial number, instead of the formatted date.

You therefore need to change the formatting of the cell to display a date. The easiest way to do this is to select the cell(s) to be formatted and then select the Date cell formatting option from the drop-down menu in the 'Number' group on the Home tab of the Excel ribbon (see below):

Excel Format Cells Drop Down Menu on Ribbon With Date Option Selected

See the Excel Formatting page for more details on formatting in Excel.