Excel DATE Function (Build a Date Serial) – Examples & Practice
Practice the Excel DATE function online with an interactive grid, instant feedback, and clear formula help.
Instruction
Create the date 15 Jul 2026 from numeric year, month, and day.
Formula Syntax
=DATE(year, month, day)
- year: Four-digit year (prefer explicit values or cell references).
- month: Month number 1–12 (Excel may roll across months when out of range).
- day: Day number (Excel may roll across days/months when out of range).
What it does
DATE builds an Excel date serial from numeric year, month, and day parts. It is the clearest way to express a calendar date inside formulas.
Excel DATE Function Examples
Build July 15, 2026
=DATE(2026, 7, 15)
Creates the correct serial from parts.
First day of month
=DATE(2026, 7, 1)
Explicit month start dates for reporting windows.
date-calendar.xlsx
| A | B | |
|---|---|---|
| 1 | Date | Offset Months |
| 2 | 2026-01-15 | 0 |
| 3 | 2026-02-28 | 1 |
| 4 | 2026-03-31 | -1 |
| 5 | 2026-07-04 | 2 |
| 6 | 2026-12-20 | 3 |
| 7 | Output |
Input Formula
Need Help?
Tips
- Store reporting parameters (year, month) in named cells.
- Format results as dates for readability.
- Pair with EOMONTH for month-end series.
DATE Function Use Cases
- Readable month boundaries in SUMIFS/COUNTIFS
- Standardize imports that split year/month/day
- Avoid ambiguous text dates
- Combine with EOMONTH for period modeling
- Teaching how Excel stores dates as numbers
Common mistakes - DATE function not working
- Hard-coding years in every formula instead of using input cells
- Assuming DATE returns text
- Mixing 2-digit years without a policy
- Forgetting leap-year sanity checks on Feb 29 logic
- Using DATE when parsing needs Power Query instead
FAQ
Does DATE return text?
DATE returns a date serial number; format the cell as a date to display it readably.
What if month is out of range?
Excel adjusts across months/years in many cases—test edge inputs.
DATE vs EDATE?
DATE builds a calendar date from parts; EDATE shifts an existing date by months.
Can I use DATE in criteria?
Yes. DATE is helpful for readable SUMIFS/COUNTIFS boundaries.
Why is my DATE wrong?
Common issues include wrong year assumptions or mixing text with numbers.
Comparison
| Tool | Role |
|---|---|
| DATE | Build from parts |
| TODAY | Rolling current date |
| EDATE | Shift months |
Example
=DATE(2026,5,3)
Advanced examples
Rolling month start
=DATE(YEAR(TODAY()), MONTH(TODAY()), 1) anchors analyses to the current month start.