Excel DATE Function


Basic Description

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

The syntax of the function is :

DATE( year, month, day )

Date Function Examples

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

 Formulas:
  A B C D
1 Day Month Year Date
2       =DATE( 2001, 1, 2 )
3 31 5 1998 =DATE( C3, B3, A3 )
4 21 5 1984 =DATE( C4, B4, A4 )
5 9 1 2012 =DATE( C5, B5, A5 )
 Results:
  A B C D
1 Day Month Year Date
2       02-Jan-2001
3 31 5 1998 31-May-1998
4 21 5 1984 21-May-1984
5 9 1 2012 09-Jan-2012

Month and Day Arguments

Typically, the month will be between 1 and 12 and the day will be between 1 and 31. However, these values can extend below or above these ranges, in which case, they behave as follows:

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 can be found on the Microsoft Office website.


Date Function Common Problem

Common Problem:

The result of your Date function looks like a number (eg. "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.

To correct this,

  • Highlight the cell(s) with the wrong formatting
  • Right click with the mouse
  • Select the Format Cells ... option and ensure the Number tab is selected
  • Under the Category heading, select the option Date and click OK

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

Return to the Excel Date and Time Functions Page

Return to the List of All Built-In Excel Functions

Valid XHTML 1.0 Transitional Valid CSS!
Disclaimer   Privacy Policy
Copyright © 2008-2014 ExcelFunctions.net