The Excel DATE function, explained interactively.
DATE builds a date value from three integers — year, month, and day. Its surprising feature is rollover: DATE(2025, 13, 1) doesn’t error; it returns 1 January 2026. Same rule for day = 0 (last day of previous month) and negative values.
How to use DATE
- Type
=DATE(and supply the year — always four digits. Values 0-1899 get adjusted weirdly; avoid them. - Add a comma and the month (1–12 in range, or pass 13+ to roll into the next year; 0 or negative rolls backwards).
- Add a comma and the day. The same rollover rule applies: 32 in a 30-day month rolls to the next month’s first day; 0 returns the last day of the previous month.
- Close the parenthesis. The cell shows a serial number internally but renders as a date when the cell is formatted as date (Ctrl+Shift+3 in Excel).
Try the demo — flip month to 13 and watch the year jump forward, or set day to 0 for a classic month-end lookup.
13 rolls into next January; 0 rolls to previous December; negative values keep walking backward.0 is last day of previous month (useful for month-end calcs); 32 in March rolls to April 1st. Negative days step back day by day.| A | B | |
|---|---|---|
| 1 | Arguments | Resolved date |
| 2 | year = 2026, month = 4, day = 19 | 2026-04-1919 April 2026 |
| 3 | ||
| 4 | Rollover? | No — every argument in range |
=DATE(2026, 4, 19). DATE(2026, 4, 19) resolves exactly to 19 April 2026 — every argument is in range.DATE syntax and arguments
Three integer arguments, all required. See Microsoft’s official DATE reference for the canonical specification.
13 becomes the next year’s January, 0 becomes the previous year’s December. The rollover is by design, not an error.0 returns the last day of the previous month — a popular idiom for month-end arithmetic without EOMONTH.DATE examples
Four patterns that cover nearly every real-world DATE use.
Example 1: DATE from three cells
The canonical case — rebuild a date from separate year, month, and day columns.
Common after importing CSVs where the date was split. If the source columns are text, wrap each with VALUE() to coerce — otherwise DATE returns #VALUE! when it sees text where a number belongs.
Example 2: DATE for the last day of a month
Need the last day of February 2025? Pass day = 0 and month = March.
Returns 28 February 2025 (since 2025 is not a leap year).EOMONTH is cleaner for explicit month-end arithmetic, but the day-zero trick works in every Excel and Sheets version.
Example 3: DATE arithmetic — add 90 days
DATE composes naturally with arithmetic. Add a fixed number of days to get a deadline.
Day 90 days after the source works thanks to rollover: DATE handles month and year overflow for you. A simpler equivalent is =A2 + 90 since Excel dates are serial numbers, but the explicit form documents intent.
Example 4: DATE for a fiscal year start
Build the start of a shifted fiscal year from the current row’s year.
Returns 1 July of the year in A2. Combine with IF(MONTH(A2)>=7, YEAR(A2), YEAR(A2)-1) for a fiscal-year calculation that starts in July and uses the correct reporting year for months before July.
Common DATE errors and fixes
Four failure modes, each with what to check and how to recover.
DATE returns #VALUE!
Cause: one of the arguments is text that can’t be coerced to a number.
Wrap text inputs with VALUE() or -- (double-unary) to force numeric coercion. Also check for stray whitespace; TRIM(A2) before passing helps.
DATE returns #NUM!
Cause: year is outside 1900-9999 in Excel (1899-12-30 onward in Google Sheets).
For pre-1900 historical dates in Excel, store as text or use a 1900-anchored offset column. For far-future dates, keep the year under 9999 or switch to custom text storage.
DATE showing a serial number instead of a date
Cause: the cell isn’t formatted as date. DATE always returns a serial; formatting decides the display.
Apply a date format: Ctrl+Shift+3 in Excel, or Format → Number → Date in Sheets. The stored value is unchanged; only rendering switches.
DATE off by one in two-digit years
Cause: passing a two-digit year. Excel adds 1900 silently, so DATE(25, 1, 1) becomes 1925-01-01, not 2025-01-01.
Always use four-digit years. If importing from a source that gives two digits, compute explicitly: DATE(2000 + A2, B2, C2).
DATE vs TODAY, EDATE & EOMONTH
Four date-builders. Pick based on whether the date is explicit, current, month-shifted, or month-end.
| Function | Inputs | Returns | Use when |
|---|---|---|---|
| DATE | year, month, day | Explicit date | You know the exact Y/M/D combination. |
| TODAY | (none) | Today’s date | Dashboards and age calcs — recalculates each open. |
| EDATE | start_date, months | Date shifted N months | Add/subtract whole months while preserving day-of-month. |
| EOMONTH | start_date, months | Last day of month after N months | Month-end reporting, fiscal boundaries, billing cycles. |
Rule of thumb: DATE builds a known date from pieces; TODAY gives “now”; EDATE and EOMONTH move forward/ backward by whole months. DATE(year, month, 0) replicates EOMONTH’s previous-month-end behaviour without the extra function, but EOMONTH is still clearer for explicit month-end math.
DATE frequently asked questions
6.01How does DATE handle a month value above 12?▸
It rolls over. DATE(2025, 13, 1) returns 1 January 2026 — the extra month carries into the year. DATE(2025, 25, 1) returns 1 January 2027. The same rule applies to negative months (0 → previous December, -1 → previous November).
6.02How do I get the last day of a month with DATE?▸
Pass day = 0 and month as the month after the one you want: =DATE(2025, 3, 0) returns the last day of February 2025. This is Excel’s idiomatic month-end trick, though EOMONTH is cleaner for explicit month-end arithmetic.
6.03Why does DATE return a number like 45000?▸
Excel stores dates as serial numbers counting days since 1 January 1900. DATE returns the serial; if the cell isn’t formatted as a date, you see the raw integer. Apply a date format to the cell (Ctrl+Shift+3 in Excel) and the display snaps to a readable date.
6.04What’s the difference between DATE and TODAY?▸
TODAY() takes no arguments and returns the current date, recalculated every time the sheet opens. DATE builds any date you specify from its three integer arguments. Use TODAY for “right now”, DATE when the value is a known year/month/day combination.
6.05Can DATE build a date in a year before 1900?▸
Excel’s date system starts 1 January 1900, so pre-1900 dates aren’t supported — DATE(1800, 5, 1) returns #NUM!. Google Sheets supports dates back to 1899-12-30. For historical work in Excel, store dates as text or use a 1900-anchored offset column.
Microsoft Excel is a registered trademark of Microsoft Corporation. Google Sheets is a trademark of Google LLC. Formula Gym is not affiliated with, endorsed by, or sponsored by either company.