Excel DATE Function: Build a Date From Year, Month and Day
Assembles a real date from three separate numbers.
DATE takes a year, a month and a day as three numbers and returns a date Excel understands. It exists because dates arrive split across columns more often than anyone would like — exports that store the year in one field and the month in another, or forms that ask for three dropdowns.
Its most useful behaviour is one that looks like a bug. Arguments outside the normal range roll over rather than erroring: month 13 becomes January of the next year, and day 0 becomes the last day of the previous month. That makes =DATE(2024, 3, 0) the last day of February without you needing to know whether it is a leap year.
It is also the correct way to write a fixed date inside a formula. Typing "01/03/2024" into a comparison depends on the machine's regional settings and will read as 3 January somewhere; DATE(2024, 3, 1) means the same thing everywhere.
Syntax
=DATE(year, month, day)Arguments
yearRequired- A four-digit year. Two-digit values are interpreted, badly — 24 becomes 1924, not 2024, so always write it in full.
monthRequired- 1 to 12 normally. Values outside that roll into adjacent years, which is a feature rather than an error.
dayRequired- 1 to 31 normally. Zero gives the last day of the previous month, and values past the month end roll forward.
The example data
Headers in row 1, data in A2:D5. Column B holds real dates; C and D hold numbers.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Order | Placed | Year | Month |
| 2 | SO-4417 | 2024-03-15 | 2024 | 3 |
| 3 | SO-4418 | 2024-06-30 | 2024 | 6 |
| 4 | SO-4419 | 2024-11-04 | 2024 | 11 |
| 5 | SO-4420 | 2025-01-28 | 2025 | 1 |
Worked examples
=DATE(C2, D2, 1)Result: 2024-03-01
The first of the month, assembled from the year and month columns.
=DATE(C2, D2 + 1, 0)Result: 2024-03-31
The month-end trick: day 0 of the next month is the last day of this one, leap years handled automatically.
=DATE(2024, 13, 1)Result: 2025-01-01
Month 13 rolls into the next year rather than erroring, which makes date arithmetic simple.
=B2 >= DATE(2024, 1, 1)Result: TRUE
Comparing against a fixed date. Writing DATE() rather than a quoted string keeps it correct on any regional setting.
Now practise it
Reading about a formula is not the same as writing one. Open this function's exercise and type it into a real grid — you get instant feedback on exactly which cell is wrong and why.
Common errors and how to fix them
Result shows as 45366
Why it happens: DATE returns a serial number and the cell is formatted as General.
How to fix it: Format the cell as a date. The value is already right.
Year comes out as 1924
Why it happens: A two-digit year was passed. Excel maps 0-29 to 2000-2029 and 30-99 to 1930-1999.
How to fix it: Always pass four digits.
#VALUE!
Why it happens: One of the three arguments is text rather than a number.
How to fix it: Wrap the source in VALUE, or fix the column. This is common when a CSV import leaves everything as text.
#NUM!
Why it happens: The resulting date falls before 1900 or after 9999.
How to fix it: Excel cannot represent dates before 1 January 1900 at all — historical data needs a different approach.
Tips worth knowing
- =DATE(YEAR(A2), MONTH(A2), 1) is the first of a date's month; EOMONTH(A2, -1)+1 does the same more briefly.
- =DATE(y, m+1, 0) is a month end that works without EOMONTH, useful in very old workbooks.
- Always use DATE for fixed dates in comparisons rather than quoted text, which is locale-dependent.
- DATEVALUE converts a text date to a real one; DATE builds one from numbers. They solve different problems.
Frequently asked questions
How do I combine separate year, month and day columns into a date?
=DATE(A2, B2, C2), pointing each argument at its column. If the columns contain text rather than numbers, wrap each in VALUE first.
Why does DATE accept a month of 13?
Because rolling over is more useful than erroring. Month 13 of 2024 is January 2025, which means you can add months with simple arithmetic — DATE(y, m+3, d) is three months later without any special handling of year boundaries.
How do I get the last day of a month without EOMONTH?
Ask for day zero of the following month: =DATE(YEAR(A2), MONTH(A2)+1, 0). Day 0 means the day before the 1st, which is the previous month's last day, and leap years are handled for you.
Related functions
Guides that use it
Longer reads where this function does real work in a real sheet.