DATE
Creates a date from separate year, month, and day values. Returns a serial number representing the date.
DATE(year, month, day)Arguments
yearThe year (1900-9999)monthThe month (1-12)dayThe day (1-31)
=DATE(2024,12,25)Creates Christmas 2024 date
=DATE(A1,B1,C1)Builds date from cell values
=DATE(2024,1,0)Day 0 returns last day of previous month
- •Month values over 12 roll over to next year
- •Day values over 31 roll over to next month
- •Use negative values to go backwards in time
- •Passing a two-digit year like 24 expecting Excel to read it as 2024 - year values under 1900 get 1900 added to them automatically, so 24 becomes 1924
- •Passing a month or day value greater than expected, like month 13, and being surprised Excel rolls it into the next year instead of returning an error
- •Building DATE from text values without converting them to numbers first, which can cause a #VALUE! error or an unexpected result
Why does DATE(24,1,1) return 1924 instead of 2024?
For year values between 0 and 1899, Excel adds 1900 to them automatically, so 24 becomes 1924. Always use the full four-digit year, like DATE(2024,1,1), to avoid this.
What happens if I pass month 13 to DATE?
Excel rolls it over - DATE(2026,13,1) returns January 1, 2027, treating the overflow as additional months rather than throwing an error.
Can DATE combine values from separate year, month, and day columns?
Yes, that's its main use case: =DATE(A1,B1,C1) where each column holds one part of the date.
Need to translate a formula using DATE?
Use our translator to convert your complete formula