DATE Function

The DATE function creates a date using individual year, month, and day arguments. Each argument is provided as a number, and the result is a serial number that represents a valid Excel date. Apply a date number format to display the output from the DATE function as a date.

In general, the DATE function is the safest way to create a date in an Excel formula, because year, month, and day values are numeric and unambiguous, in contrast to text representations of dates which can be misinterpreted. 

Note: to move an existing date forward or backward in time, see the EDATE and EOMONTH.

Example #1 - hard-coded numbers

For example, you can use the DATE function to create the dates January 1, 1999, and June 1, 2010, with the following syntax:

=DATE(1999,1,1) // returns Jan 1, 1999
=DATE(2010,6,1) // returns Jun 1, 2010

Example #2 - cell reference

The DATE function is useful for assembling dates that need to change dynamically based on other inputs in a worksheet. For example, with 2018 in cell A1, the formula below returns the date April 15, 2018:

=DATE(A1,4,15) // Apr 15, 2018

If A1 is then changed to 2019, the DATE function will return a date for April 15, 2019.

Example #3 - with SUMIFS, COUNTIFS

The DATE function can be used to supply dates as inputs to other functions like SUMIFS or COUNTIFS, since you can easily assemble a date using year, month, and day values that come from a cell reference or formula result. For example, to count dates greater than January 1, 2019, in a worksheet where A1, B1, and C1 contain year, month, and day values (respectively), you can use a formula like this:

=COUNTIF(range,">"&DATE(A1,B1,C1))

The result of COUNTIF will update dynamically when A1, B1, or C1 are changed.

Example #4 - the first day of the current year

To return the first day of the current year, you can use the DATE function like this:

=DATE(YEAR(TODAY()),1,1) // first of year

This is an example of nesting. The TODAY function returns the current date to the YEAR function. The YEAR function extracts the year and returns the result to the DATE function as the year argument. The month and day arguments are hard-coded as 1. The result is the first day of the current year, a date like "January 1, 2021".

Note: the DATE function actually returns a serial number and not a formatted date. In Excel's date system, dates are serial numbers. January 1, 1900, is number 1 and later dates are larger numbers. To display date values in a human-readable date format, apply the number format of your choice.

Notes

• The DATE function returns a serial number that corresponds to an Excel date.
• Excel dates begin in the year 1900. If the year is between zero and 1900, Excel will add 1900.
• The DATE function accepts numeric input only and will return #VALUE if given text.