How to Use DATE Function in Excel: Syntax and Examples
In Excel, use the
DATE function to create a date from separate year, month, and day numbers. The syntax is =DATE(year, month, day), which returns a valid date value you can format or use in calculations.Syntax
The DATE function has three parts:
- year: The full year as a number (e.g., 2024).
- month: The month number (1 for January, 12 for December).
- day: The day number within the month.
Excel combines these to create a date value you can use in your sheet.
excel
=DATE(year, month, day)
Example
This example creates the date April 15, 2024, using the DATE function.
excel
=DATE(2024, 4, 15)
Output
4/15/2024
Common Pitfalls
Common mistakes include:
- Entering the year as two digits (e.g., 24 instead of 2024) which Excel may interpret as 1924.
- Using month or day numbers outside their valid ranges (e.g., month 13 or day 32).
- Not formatting the cell as a date, so the result shows as a number.
Excel can handle months or days outside normal ranges by rolling over to the next month or year.
excel
=DATE(24, 13, 32) <em>returns</em> 2/1/1926 (Excel adds extra months and days automatically)
Output
2/1/1926
Quick Reference
| Parameter | Description | Example |
|---|---|---|
| year | Full year number | 2024 |
| month | Month number (1-12) | 4 |
| day | Day number (1-31) | 15 |
Key Takeaways
Use =DATE(year, month, day) to create valid dates from numbers.
Excel adjusts months and days outside normal ranges automatically.
Always enter the full year (e.g., 2024) to avoid confusion.
Format the cell as a date to see the result properly.
DATE is useful for building dates from separate year, month, and day inputs.