Excel DATE Function

The Excel DATE function combines separate year, month and day values and converts them into a date serial number formatted as a date.

Excel DATE Function Syntax

The example below illustrates how you can gather day, month and year components from separate cells and use the DATE function to return a date serial number:

Tip: We can use the DATE function to add or subtract months or years to a date. See below.

Tip: We can also use the EDATE function to roll dates forward by a set number of months.

Note: If we add days and they exceed the number of days in the month, the DATE function will add the excess days to the start of the following month. See examples below.

Excel DATE Function Tips

Tip 1: The Year argument must be a positive value, but month and day can be negative! If month is greater than 12, month adds that number of months to the first month in the year specified.

Tip 2: If month is less than 1, month subtracts the magnitude of that number of months, plus 1, from the first month in the year specified.

Tip 3: If day is 0, DATE will return the last day of the previous month. e.g. =DATE(2019,1,0) returns December 31, 2018. See more examples below.

Want More

Learn more about how Excel handles dates and time in our comprehensive guide to working with Excel Date and Time, or download the files below.

Free eBook - Working with Date & Time in Excel

Everything you need to know about Date and Time in Excel - Download the free eBook and Excel file with detailed instructions.