Excel DATE Function
The Excel DATE function creates a valid date from 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.
- year - Number for year.
- month - Number for month.
- day - Number for day.
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.
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:
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:
The result of COUNTIF will update dynamically when A1, B1, or C1 are changed.
Example #4 - first day of current year
To return the first day of the current year, you can use the DATE function like this:
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.
- The DATE function returns a serial number that corresponds to an Excel date.
- Excel dates begin in the year 1900. If year is between zero and 1900, Excel will add 1900.
- The DATE function accepts numeric input only and will return #VALUE if given text.