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.
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.
Enter your email address below to download the comprehensive Excel workbook and PDF.
please send me details for pivot tables and if commands.
Not sure what you mean by ‘details’. You can learn PivotTables here. And IF formulas here.