The Excel DATE function assembles a real, calculation-ready date from three separate numbers: a year, a month, and a day. It is the safe way to build dates inside formulas — no typing "6/15/2026" as text and hoping Excel guesses right — and its habit of rolling overflow forward (month 13 becomes January of the next year) is the foundation of almost every date-math trick in Excel.
=DATE(A2, B2, C2)) to turn split-up year/month/day columns into real dates.
Syntax
| Argument | Description | |
|---|---|---|
year | Required | A four-digit year (1900–9999). Values 0–1899 are added to 1900, so DATE(26, 6, 15) means 1926, not 2026 — always pass four digits. |
month | Required | The month number. Values outside 1–12 roll into adjacent years: 13 = January next year, 0 = December of the previous year. |
day | Required | The day number. Values outside the month’s range roll forward or back: day 0 = the last day of the previous month. |
How Excel stores dates: every date is a serial number — the count of days since January 1, 1900 (serial 1). June 15, 2026 is serial 46188, and times are fractions of a day (0.5 = noon). DATE returns that serial number; the cell’s number format decides whether you see 46188 or 06/15/2026. Most date confusion in Excel traces back to this one idea.
Build dates from year, month, and day columns
Imported data often arrives with the year, month, and day in separate columns — or buried inside a text code. DATE stitches the pieces back into a genuine date that sorts, filters, and calculates correctly:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Year | Month | Day | =DATE(A,B,C) |
| 2 | 2026 | 6 | 15 | 06/15/2026 |
| 3 | 2026 | 12 | 31 | 12/31/2026 |
| 4 | 2026 | 13 | 1 | 01/01/2027 |
| 5 | 2026 | 3 | 0 | 02/28/2026 |
Notice rows 4 and 5: month 13 rolled into January 2027, and day 0 rolled back to the end of February. That is not a bug — it is the feature the next sections are built on.
To pull date parts out of an 8-digit text code like 20260615, slice it with text functions and feed the pieces to DATE:
Try it: interactive DATE demo
Step the year, month, and day — push month past 12 or day past the end of the month and watch DATE roll the overflow forward, exactly like Excel.
The overflow tricks: first, last, next
Because DATE rolls out-of-range months and days instead of complaining, you can do date arithmetic without knowing how many days each month has:
The "day 0" trick handles leap years automatically: =DATE(2028, 3, 0) returns February 29, 2028. For adding months, note that EDATE is usually the better tool — it clamps January 31 + 1 month to February 28, while the DATE version above rolls it into March 2–3. And for "last day of the month," EOMONTH says the same thing in fewer characters.
Errors & common pitfalls
#NUM! — the result falls outside Excel’s calendar. If the combination works out to a date before January 1, 1900 (e.g. =DATE(1899, 12, 31) after roll-back) or a year above 9999, DATE returns #NUM!.
Pitfall: two-digit years. =DATE(26, 6, 15) is June 15, 1926 — Excel adds years 0–1899 to 1900. If your year column holds two-digit values, convert first: =DATE(IF(A2<30, 2000+A2, 1900+A2), B2, C2).
Pitfall: the answer looks like 46188, not a date. DATE returns a serial number; if the cell was formatted as General or Number you see the raw serial. Press Ctrl+1 and pick a Date format — the value is already correct.
Pitfall: building dates with & instead of DATE. =A2 & "/" & B2 & "/" & C2 produces text that only looks like a date — it won’t sort chronologically or do math. Always assemble with DATE so the result is a real serial number.
Practice workbook
Frequently asked questions
Why does DATE(2026, 13, 1) work instead of giving an error?
Why does my DATE formula show a number like 46188?
How do I get the last day of a month with DATE?
=DATE(YEAR(A2), MONTH(A2)+1, 0). Day 0 rolls back one day, landing on the last day of A2’s month — leap years included. EOMONTH does the same job with less typing.Should I add months with DATE or with EDATE?
DATE(YEAR(d), MONTH(d)+n, DAY(d)) when you want strict day-for-day rolling, where the same example lands on March 2 or 3.Why did DATE turn my year 26 into 1926?
How do I convert a text date into a real date?
=DATE(LEFT(A2,4), MID(A2,5,2), RIGHT(A2,2)).Master functions like this in one day
This page covers one function. Our Excel Formulas and Functions class covers the 30 that matter most — live, hands-on, taught by professionals in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class