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:
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: 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.
Notes
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.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
