Formula Guide

The Excel DATE Function
explained simply

DATE builds a real date value from separate year, month, and day numbers — useful for constructing dates dynamically inside formulas.

ExcelPro · 4 min read · Updated June 2026
Contents
  1. What does DATE do?
  2. Syntax
  3. 4 examples
  4. Common mistakes
  5. FAQ

What does DATE do?

DATE takes a year, month, and day as three separate numbers and combines them into one real, working date value.

It is most useful when those three pieces come from different cells or calculations rather than being typed directly — letting you construct a date that updates if any of its parts change.

Syntax

=DATE(year, month, day)
ArgumentDescription
year requiredThe year, as a 4-digit number.
month requiredThe month, 1–12.
day requiredThe day of the month.

Examples

Example 1
Build a date from three cells
=DATE(A2,B2,C2)

If A2=2026, B2=6, C2=22, returns June 22, 2026 as a real date.

Example 2
Start of the current year
=DATE(YEAR(TODAY()),1,1)

Builds January 1st of whatever year it currently is.

Example 3
Add months by overflowing the month argument
=DATE(2026,13,1)

Excel correctly rolls this over to January 1, 2027 — DATE automatically handles out-of-range months and days.

Example 4
Filter SUMIFS by a date range
=SUMIFS(C:C,A:A,">="&DATE(2026,1,1),A:A,"<="&DATE(2026,3,31))

DATE is commonly used inside SUMIFS/COUNTIFS criteria to build a clean date boundary.

Common mistakes

⚠️ Typing a 2-digit year

DATE(26,6,22) does not mean 2026 — always use the full 4-digit year to avoid ambiguity.

⚠️ Forgetting DATE handles overflow gracefully

Rather than being an error, month=13 or day=32 rolls over into the next year/month — sometimes this is exactly what you want, sometimes it hides a mistake.

FAQ

Can DATE build a date from text instead of numbers?
No — DATE needs numeric year/month/day. Use DATEVALUE to convert a text date string into a real date instead.

Practise Excel with real data

ExcelPro has 700+ hands-on Excel exercises across 7 career tracks — free to start, no download needed.

Start practicing free →

Related formulas

DATEVALUE YEAR DAY