For IndividualsFor Analysts & FinanceFor Students & LearnersFor Teams & Automation
MENU
=FUNCTIONS

DATE()

Date & Time

Builds a date from separate year, month, and day numbers.

DATE(year, month, day)

DATE assembles a proper date serial number from three numbers, which is useful when year/month/day come from separate cells or formulas rather than being typed as a date directly.

It tolerates out-of-range values by rolling over: DATE(2026, 13, 1) becomes January 1, 2027, which makes it handy for date math like "the first of next month."

DATE is the safe way to construct a date because it never depends on regional settings — DATE(2026,3,4) means the fourth of March everywhere, while the text "03/04/2026" means different things in different places. That makes it the right building block inside criteria, where a date written as text will silently match nothing. Its rollover behaviour is deliberate and useful: month 13 becomes January of the next year and day 0 becomes the last day of the previous month, so month arithmetic works without any checking of month lengths.

Arguments

year
The year, e.g. 2026.
month
The month as a number, 1–12 (or outside that range, which rolls over into adjacent years).
day
The day of the month.

Examples

=DATE(2026, 7, 24)
→7/24/2026

Builds a date from three separate numbers.

=DATE(YEAR(TODAY()), MONTH(TODAY())+1, 1)
→First of next month

A common pattern for rolling date math.

=DATE(2026, 13, 1)
→01 Jan 2027

Out-of-range arguments roll over rather than erroring. Month 13 becomes January of the next year, which makes DATE safe for month arithmetic.

Notes and gotchas

  • DATE builds a date from three numbers, which makes it immune to the locale ambiguity that plagues typed dates — DATE(2026,3,4) is unambiguous where 03/04/2026 is not.
  • It handles out-of-range parts by rolling over: DATE(2026,13,1) is January 2027, and DATE(2026,3,0) is the last day of February. The zero-day trick is a compact month-end formula.
  • A two-digit year argument is interpreted as 1900-something or 2000-something depending on the value, which is a real hazard when the year comes from parsed text. Always pass four digits.
  • This is the right way to build a date criterion for SUMIFS: ">="&DATE(2026,1,1), since a date written inside quotes is read as text and matches nothing.
  • The result is a serial number; format the cell as a date or you will see a five-digit integer.

Common errors

What goes wrong with DATE, and why
You seeBecauseFix
#NUM!The resulting date is before 1 January 1900, Excel's epoch.Excel cannot represent earlier dates as serial numbers. Store historical dates as text if you need them.
#VALUE!A non-numeric argument.Wrap parsed text parts in VALUE first.
Tips / Tricks / Hacks

DATE tricks, tips, and workbook hacks

DATE constructs a valid, locale-independent Excel serial date from three separate numbers: year, month, and day. Unlike typing "03/04/2026" (which means March 4 in the US and April 3 in the UK), =DATE(2026, 3, 4) is unambiguous everywhere in the world.

DATE also features built-in calendar rollover arithmetic: pass month = 13 and it rolls into January of the next year; pass day = 0 and it steps back to the very last day of the prior month!

Quick DATE checks before trusting the result

  • Always build hardcoded date constants inside formulas with DATE(2026, 1, 1) rather than quoted strings like "1/1/2026".
  • Use =DATE(YEAR(A2), MONTH(A2), 1) to snap any daily transaction date back to the 1st of its month.
  • Use =DATE(YEAR(A2)+1, MONTH(A2), DAY(A2)) to calculate a 1-year contract renewal date.
  • Always ensure the year argument is a full 4-digit year (2026, not 26), because DATE adds 1900 to values between 0 and 1899.
DATE tricks and formula patterns
DATE patternDATE formulaBest useWatch out
Reassemble YYYYMMDD integer exports into true Excel dates=DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))Converting ERP or SQL integer dates like 20260315 into real Excel dates you can filter and pivot.DATE automatically coerces the text outputs of LEFT/MID/RIGHT into numbers.
First day of the current month from any date=DATE(YEAR(A2),MONTH(A2),1)Normalizing daily invoice dates into monthly cohort buckets.Format the cell as mmm yyyy so all rows in March display "Mar 2026" while storing 2026-03-01.
Last day of the month using the day = 0 rollover trick=DATE(YEAR(A2),MONTH(A2)+1,0)Finding month-end dates (including Feb 28 vs Feb 29 leap years) by asking for day 0 of the following month.EOMONTH(A2, 0) achieves the same result with a shorter formula.
Common DATE mistakes and how to fix them
DATE mistakeWhat happens in DATEFix
Passing a 2-digit year like 26=DATE(26, 3, 15) produces March 15, 1926 instead of 2026 because DATE treats years < 1900 as offsets from 1900.Add 2000 to 2-digit years (=DATE(2000+A2, ...)) or enforce 4-digit year inputs.

DATE practice drills

  1. Convert a column of 8-digit numbers (20260115, 20260228, 20261130) into formatted Excel dates using DATE + LEFT/MID/RIGHT.

Related functions