DATE assembles a date from year, month and day — and rolls over out-of-range parts instead of erroring. Day 32 becomes next month; month 0 becomes December of the prior year. This page measures the rollover rules, leap years, and date serials live.
Returns a date serial. Out-of-range month/day parts roll over rather than erroring; wrap in TEXT to display a format.
DATE assembles a date from year, month and day — and it rolls over out-of-range parts instead of erroring. Day 32 becomes next month; month 0 becomes December of the prior year. Every row measured live. Click any row to load it.
| Expression | Result | Why |
|---|---|---|
| =TEXT(DATE(2024,1,15),"yyyy-mm-dd") | 2024-01-15 | Assemble a date from parts |
| =TEXT(DATE(2024,1,32),"yyyy-mm-dd") | 2024-02-01 | Day 32 rolls over to Feb 1 |
| =TEXT(DATE(2024,0,1),"yyyy-mm-dd") | 2023-12-01 | Month 0 rolls to Dec of prior year |
| =TEXT(DATE(2024,13,1),"yyyy-mm-dd") | 2025-01-01 | Month 13 rolls to Jan of next year |
| =TEXT(DATE(2024,2,29),"yyyy-mm-dd") | 2024-02-29 | Leap year keeps Feb 29 |
| =TEXT(DATE(2023,2,29),"yyyy-mm-dd") | 2023-03-01 | Non-leap Feb 29 rolls to Mar 1 |
| =DATE(2024,1,15) | 45306 | The underlying date serial number |
| =TEXT(DATE(2024,3,0),"yyyy-mm-dd") | 2024-02-29 | Day 0 = last day of the prior month |
| Expression | Result | Why |
|---|---|---|
| =DATE(2024,1,15) | 45306 | a date is stored as a day count |
| =TEXT(DATE(2024,1,15)+30,"yyyy-mm-dd") | 2024-02-14 | add days with plain arithmetic |
Edit the table or type any formula — the engine evaluates instantly. Load a preset to see DATE roll over day 32 and month 0, keep a leap day, and add days with arithmetic.
8 failure patterns mined from actual user reports on Reddit, Stack Overflow and Super User — each with the authentic symptom, root cause, a fix, and a runnable formula. Not textbook theory.
DATE is available in Excel 2003+, Microsoft 365, Excel for the web and Google Sheets. This page uses numeric date formats only; pre-1900 serial boundaries are prose-only because engines differ there.
| Platform | Support | Notes | Gotcha |
|---|
DATE is one piece. The full course walks the toolkit end to end with live, measured examples.