DATE function
DATE builds a real date from separate year, month and day numbers and returns a date serial number. It is handy for assembling dates from split columns or shifting a date by adding to the month or day.
Example
Read it as a sentence: turn the year 2024, month 1, and day 31 into a date, which shows as the serial number 45322 until you format it.
A
B
C
D
1
45322
2
3
Syntax
DATE(year, month, day)
-
year(required) - Year number; 1900 to 9999.
-
month(required) - Month number; values over 12 roll into later years, values below 1 roll back.
-
day(required) - Day of the month; overflow or zero rolls into neighbouring months.
Returns: A date serial number (format the cell as a date to see it).
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=DATE(2026,1,9) | 1/9/2026 | Basic |
=DATE(2026,13,1) | 1/1/2027 | Month past 12 rolls into next year |
=DATE(2026,1,0) | 12/31/2025 | Day 0 |
=DATE(2026,0,1) | 12/1/2025 | Month 0 |
=DATE(2026,2,30) | 3/2/2026 | Day past month end |
=DATE(1900,2,29) | 2/29/1900 | Leap-year bug date |
=DATE(1900,1,-1) | #NUM! | Before serial 0 |
=DATE(-1,1,1) | #NUM! | Negative year |
Watch out for
- Result shows as a plain number until the cell is formatted as a date.
- Months and days out of range roll over instead of erroring, so DATE(2024,14,1) is February 2025.
- A year between 0 and 1899 is added to 1900, which gives odd results.
Where it works
- Excel
- DATE is in every version of Excel from 2007 on.
- Google Sheets
- Same name, same behaviour for everyday use.
Part of Date and time functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .