ID EN
…
#Date and time

DATE

Excel Functions

Create a date with year, month, and day

Syntax

EXCEL 
=DATE(year, month, day)

Arguments

Parameter Description
year Number for year.
month Number for month.
day Number for day.

Return Value

A valid Excel date

Details

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. For example, you can use the DATE function to create the dates January 1, 1999, and June 1, 2010, with the following syntax: The DATE function is useful for assembling dates that need to change dynamically based on

Examples

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:

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

EXCEL 
=DATE(A1,4,15) // Apr 15, 2018
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, m

EXCEL 
=COUNTIF(range,">"&DATE(A1,B1,C1))
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:

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

See Also

TIME YEAR MONTH DAY EDATE