Excel DATE Function • My Online Training Hub

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

Syntax:	=DATE(year, month, day)
year	The value of the *year* argument can include one to four digits from 1900 to 9999.
month	A positive or negative integer representing the month of the year from 1 to 12 (January to December).
day	A positive or negative integer representing the day of the month from 1 to 31.

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.

add or subtract time with Excel DATE Function

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.

add days to dates with Excel DATE Function

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.

subtract days to dates with Excel DATE Function

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.

Enter your email address below to download the comprehensive Excel workbook and PDF.

By submitting your email address you agree that we can email you our Excel newsletter.

Please enter a valid email address.

Download the Excel Workbook and PDF. Note: This is a zip file including an Excel workbook with detailed instructions and a PDF version for your reference.

2 thoughts on “Excel DATE Function”