Quick Navigation
DATE Function
Summary
The DATE function constructs a valid date from individual year, month, and day components, returning Excel's serial number representation. Perfect for building dates from separate cells or calculations, handling overflow intelligently by adjusting months and years automatically.
Syntax
DATE(year, month, day)Parameters
| Parameter | Type | Required | Description |
|---|---|---|---|
| year | Number |
Yes | Year value (0-9999). Values 0-1899 add to 1900. Use 4 digits to avoid ambiguity. |
| month | Number |
Yes | Month number (1-12). Values >12 add months; <1 subtract months. |
| day | Number |
Yes | Day of month (1-31). Values >days in month add days; <1 subtract days. |
Using the DATE Function
DATE is essential when working with dates stored in separate columns or when performing date arithmetic that requires reconstructing dates from extracted components. Excel stores dates as serial numbers, so DATE converts logical date parts into these serial numbers for calculations.
Common DATE Examples
Basic Date Construction
=DATE(2024,12,25)Creates December 25, 2024. Returns serial number that displays as date when formatted.
Using Cell References
=DATE(C2,A2,B2)Combines year from C2, month from A2, day from B2 into single date.
Handle Month Overflow
=DATE(2024,14,15)February 15, 2025 (14 months = 1 year + 2 months).
Anniversary Calculation
=DATE(YEAR(A2)+5,MONTH(A2),DAY(A2))Employee's 5-year anniversary from hire date in A2.
Convert YYYYMMDD Text
=DATE(LEFT(C2,4),MID(C2,5,2),RIGHT(C2,2))Converts '20240314' text string into proper date.
Frequently Asked Questions
Common Errors and Solutions
#NUM!
Cause: Year <0 or >=10000
Solution: Use valid year range 0-9999
#NUM!
Cause: Invalid date combinations
Solution: DATE handles overflow automatically; check input values
Shows number not date
Cause: Cell formatted as General
Solution: Format cell as Date (Ctrl+1 > Date category)
Notes
- Always use 4-digit years to avoid 1900/2000 confusion
- DATE respects Excel's 1900 date system (Jan 1, 1900 = serial 1)
- Perfect companion to YEAR(), MONTH(), DAY() functions
- Use with TODAY() for relative date calculations
Compatibility
Available in: Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel 2021, Excel 365, Excel for Mac, Excel for Web
Not available in:
Content last reviewed: December 9, 2025
Update frequency: As needed
Excel versions tested: Excel 2007+