Date & time · build a date · Nov 16, 2026

The DATE function,
measured & explained.

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.

D1
fx
=DATE(2024, 1, 15)
0
measured gallery rows
0
sourced errors fixed
0
platforms checked
// 01 — Anatomy

How DATE works

Returns a date serial. Out-of-range month/day parts roll over rather than erroring; wrap in TEXT to display a format.

=DATE(year, month, day)
yearThe year (e.g. 2024).
monthThe month; 0 rolls to prior Dec, 13 to next Jan.
dayThe day; 0 = last day of prior month, 32 rolls over.
.bool-moat td.res-text{color:var(--cyan);font-weight:700;font-family:var(--mono)}
// 02 — Rollover rules

What DATE actually returns

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.

Rollover gallery

ExpressionResultWhy
=TEXT(DATE(2024,1,15),"yyyy-mm-dd")2024-01-15Assemble a date from parts
=TEXT(DATE(2024,1,32),"yyyy-mm-dd")2024-02-01Day 32 rolls over to Feb 1
=TEXT(DATE(2024,0,1),"yyyy-mm-dd")2023-12-01Month 0 rolls to Dec of prior year
=TEXT(DATE(2024,13,1),"yyyy-mm-dd")2025-01-01Month 13 rolls to Jan of next year
=TEXT(DATE(2024,2,29),"yyyy-mm-dd")2024-02-29Leap year keeps Feb 29
=TEXT(DATE(2023,2,29),"yyyy-mm-dd")2023-03-01Non-leap Feb 29 rolls to Mar 1
=DATE(2024,1,15)45306The underlying date serial number
=TEXT(DATE(2024,3,0),"yyyy-mm-dd")2024-02-29Day 0 = last day of the prior month

Dates are serial numbers

ExpressionResultWhy
=DATE(2024,1,15)45306a date is stored as a day count
=TEXT(DATE(2024,1,15)+30,"yyyy-mm-dd")2024-02-14add days with plain arithmetic
⚠ DATE rolls over, it does not error: DATE(2024,1,32)=Feb 1, DATE(2024,0,1)=Dec 1 2023, DATE(2024,3,0)=last day of Feb. Day 0 means the last day of the previous month — a handy month-end trick.
A date is a serial number (days since 1899-12-30). Add or subtract integers to shift days, and wrap in TEXT to display a format. DATE builds the serial from parts.
// 03 — Try it live

Interactive DATE playground

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.

A1Sample data — click any cell to edit, the formula re-evaluates live
Result
—
✓ readyHyperFormula 3.5
// 04 — Error library

The real-world error library

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.

// 05 — Cross-platform

Where DATE works

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.

PlatformSupportNotesGotcha
✓ Supported full DATE◐ Partial works with caveatsCompiled from public docs · verify against your build
// Go deeper

Master building dates

DATE is one piece. The full course walks the toolkit end to end with live, measured examples.

  • The rollover rules (day 32, month 0/13)
  • Day 0 for month-end
  • Leap-year handling
  • Dates as serial numbers and day arithmetic
Free while in beta
Browse the function library

No account needed. Every page has a live playground.

// 07 — FAQ

Frequently asked