Exceljet

Quick, clean, and to the point

Excel DATE Function

Excel DATE function
Summary 

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.

Purpose 
Create a date with year, month, and day
Return value 
A valid Excel date
Syntax 
=DATE (year, month, day)
Arguments 
  • year - Number for year.
  • month - Number for month.
  • day - Number for day.
Usage notes 

The DATE function creates a valid Excel date using individual year, month, and day components. For example, you can use the DATE function to create the dates January 1, 1999, and June 1, 2010 like this:

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

The DATE function is useful for assembling dates that need to change dynamically based on other values. For example, with 2018 in cell A1:

=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.

The DATE function is especially useful when supplying 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.

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 a the number format of your choice.

Notes

  • Excel dates begin in the year 1900. If year is between zero and 1900, Excel will add 1900 to the year.
  • Month can be greater than 12 and less than zero. If month is greater than 12, Excel will add month to the first month in the specified year. If month is less than or equal to zero, Excel will subtract the absolute value of month plus 1 (ABS(month) + 1) from the first month of the given year.
  • Day values can be positive or negative. If day is greater than the days in the given month, Excel will add day to the first day of the specified month. If day is less than or equal to zero, Excel will subtract the absolute value of day plus 1 (ABS(day) + 1) from the first day of the specified month.

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.