The Excel DATE function is a worksheet function that returns a date value from a given year, month, and day. It belongs to the date functions category in the list of Excel functions.
Function Syntax
The DATE function uses the following syntax.
=DATE(year, month, day)
Year: The year of the specified date. Can be entered using up to four digits.
Month: A positive or negative number representing the month. This integer can be used to add or subtract months from a date.
Day: A positive or negative integer representing the day of the month.
Download the sample file to practice the following DATE formula examples.
Why Use the Excel DATE Function?
You may be thinking why you would use the DATE function. Surely you can just type dates into formulas, or refer to cells containing date values.
Well here are the three most likely reasons to use DATE in Excel.
You need to enter a fixed date directly into an Excel formula.
To convert text values to a date format
To offset a date by a specified interval e.g., the date one year into the future.
In this tutorial, we will see examples for each of these reasons.
How to use the DATE Function in Excel?
To use the DATE function in Excel, you need only to provide it with the three date elements of year, month, and day, in that order.
These date parts can be input directly into the argument, come from a cell reference, or be calculated using a function such as the DAY function, or MONTH function.
In this basic DATE formula example, we type the values directly into the arguments to return the date of the 5th June 2023.
=DATE(2023,6,5)
The result is returned as a formatted date. If your result is not in formatted as a date, then apply the date formatting manually using your chosen technique. For example, using the Number Format list.
Use Cell References for Input Values
Of course, the three separate values that the DATE function requires can come from the grid.
In this example, the three separate values for the dates Excel uses are in the ordered of day, month, and year as that is the order of my local date system in the UK.
DATE requires them in year, month, and day order, so each cell reference is given in that order to the function.
=DATE(C2,B2,A2)
Interesting Ways to Use the DATE Function
I’m not sure how practical these techniques are, but they are interesting, and are covered for comprehension in understanding the DATE formula.
For the month and day argument of the DATE function, you can enter either a positive or negative integer. The use of a negative integer is demonstrated in rows 3 and 4 on the spreadsheet shown in the image.
For example, the date formula =DATE(2023,-1,10) returns the date of the 10th November 2022. If the formula =DATE(2023,0,10) were used, it would returns the 10th December 2023, so the -1 for the month returns the date for the previous month.
A #NUM! error is returned in row 5 as you cannot enter a negative value for the year argument. Excel stores dates as a serial number. This serial number must be a positive value.
The first date formula in row 2 uses the value 30 for the day argument. The month is 2 (February) and there are not 30 days in this month, so the date of the 2nd March 2023 is returned as that is two days into the following month.
The FORMULATEXT function is used in column B to show the formula entered in each cell.
So, a little confusing, a little interesting, not very practical, but important to understand when using the DATE function.
Practical Examples of DATE in Microsoft Excel
Let’s look at some practical DATE examples.
Convert Text Values to a Date Format
In this example, dates in Excel have been stored as text. The DATE formula is used to convert them to dates.
=DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))The LEFT, MID and RIGHT functions are used to extract the different elements of the text date.
Get Same Date Last Year
To return the same date last year, the YEAR function and TODAY function are used to extract the year from the current date. -1 is then used to return the previous year.
The current date as I write this article is the 5th June 2023, so the following DATE function returns the date of the 5th June 2022, as that is exactly one year prior.
=LET(
today,TODAY(),
DATE(YEAR(today)-1,MONTH(today),DAY(today))
)The LET function was used to store the result of the TODAY function as the name today. This name was then used three times in the formula but calculates once rather than thrice.
Wrap Up
The DATE function is very useful as it ensures that we correctly represent dates in Microsoft Excel, both within formulas and on the grid.
If you want to learn over 150 of the best Excel functions, grab a copy of Advanced Excel Formulas. This comprehensive book contains more than 500 formula examples supported with downloadable practice files.
