DATE Function

Builds a real date from a year, month and day.

Category

Date & Time

Available in

All versions, Microsoft 365

Returns

A date (serial number)

Taught hands-on in Level 3: Intermediate Lookups & Data Summaries · Dates & Times module

Overview

DATE turns three numbers — year, month, day — into a date Excel can calculate with. It's the safest way to put a date inside a formula, because it never depends on how a computer's regional settings read 3/4 (March 4 or April 3?).

Behind the scenes every date is a serial number — 1 is 1/1/1900 and 46096 is 3/15/2026 — so dates can be added, subtracted and compared like any other number.

Syntax

=DATE(year, month, day) Explain

Arguments

ArgumentRequired?What it does
yearYesThe year, ideally four digits (2026).
monthYesThe month, 1–12. Numbers outside that range roll into the next or previous year.
dayYesThe day of the month. Too many days roll into the next month; 0 means the last day of the month before.

Examples

Example 1 — Joining year, month and day columns

Northstar's order system exports the parts of each date in separate columns. DATE puts them back together.

D2fx=DATE(A2,B2,C2)
ABCD
1YearMonthDayOrder date
220263153/15/2026
32026123112/31/2026
42027141/4/2027
=DATE(A2,B2,C2) Explain

Example 2 — Months and days that roll over

DATE quietly fixes out-of-range months and days, which makes month-end maths easy.

B2fx=DATE(2026,14,1)
AB
1FormulaResult
2=DATE(2026,14,1)2/1/2027
3=DATE(2026,3,0)2/28/2026
4=DATE(2026,1,32)2/1/2026

Month 14 is February of the next year, and day 0 of March is the last day of February.

Example 3 — First day of the month

Pull the year and month out of any date and set the day to 1.

=DATE(YEAR(A2), MONTH(A2), 1) Explain
Swap the 1 for 0 and add 1 to the month — =DATE(YEAR(A2), MONTH(A2)+1, 0) — to get the last day of the month instead (or use EOMONTH).

Common mistakes

  • The answer shows as a number like 46096. The cell is formatted as General. Pick Short Date from the Number Format box on the Home tab.
  • Two-digit years. DATE(26,3,15) means the year 1926, not 2026 — years 0–1899 are added to 1900. Always type four digits.

Notes

Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.