
The Excel DATE function creates a valid date from the individual year, month, and day components. The DATE function is useful for assembling dates that need to change dynamically based on other values in a worksheet.
Purpose
Create a date with year, month, and day
Return value
A valid Excel date
Arguments
- year – Number for a year.
- month – Number for a month.
- day – Number for the day.
Syntax
=DATE(year, month, day)
Usage notes
The DATE function creates a date using individual year, month, and day arguments. Each argument is provided as a number, and 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 the year, month, and day values (respectively), you can use a formula like this:
=COUNTIFS (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