Excel DATE Function: Complete Guide (2026)
ExcelFunction Reference⏱ 6 min read

Excel DATE Function: Complete Guide

DATE builds a proper Excel date from three separate numeric inputs — year, month, day. It's the constructor function. And its ability to handle overflow (month 13 = next January) makes it powerful for rolling date math beyond just assembling components.

Syntax

DATE(year, month, day)
ArgumentTypeDescription
year RequiredNumberFull year (2026). Two-digit years work but interpret 0-29 as 2000-2029 and 30-99 as 1930-1999. Always use 4-digit years to avoid ambiguity.
month RequiredNumber1-12 for Jan-Dec. Values outside overflow: 13 = next January, 0 = last December, -1 = last November.
day RequiredNumber1-31 for a valid day of the month. Values outside overflow: 32 = first of next month, 0 = last day of prior month.

Basic examples

Standard date construction

=DATE(2026, 9, 6)
Result: September 6, 2026

From separate columns

=DATE(A1, B1, C1)
If A1=2026, B1=9, C1=6 → Result: September 6, 2026

Overflow — the powerful feature

Values outside the normal range don't cause errors. Excel rolls them over.

Month = 13 → next January

=DATE(2026, 13, 1)
Result: January 1, 2027

Month = 0 → last December

=DATE(2026, 0, 15)
Result: December 15, 2025

Day = 32 → first of next month

=DATE(2026, 1, 32)
Result: February 1, 2026

Day = 0 → last day of prior month

=DATE(2026, 3, 0)
Result: February 28, 2026 (great for month-end!)

Common uses

1. Convert YYYYMMDD text to a real date

Parse text like "20260906"

=DATE(LEFT(A1, 4), MID(A1, 5, 2), RIGHT(A1, 2))
If A1="20260906" → Result: September 6, 2026

2. Last day of the current month

Month-end date

=DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 0)
Uses overflow: month+1, day=0 → last day of current month

Simpler alternative: =EOMONTH(TODAY(), 0). See EOMONTH.

3. Fiscal year start

Fiscal year begins April 1

=DATE(IF(MONTH(TODAY()) >= 4, YEAR(TODAY()), YEAR(TODAY()) - 1), 4, 1)
Returns April 1 of the current fiscal year

4. First Monday of a month

Combine DATE with WEEKDAY

=DATE(2026, 9, 1) + MOD(2 - WEEKDAY(DATE(2026, 9, 1), 2), 7)
Returns the first Monday of September 2026

5. Nth day of month (birthday reminder pattern)

15th of every month, this year

=DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 15)
Next month's 15th

Two-digit year gotcha

Excel accepts 2-digit years but interprets them like Y2K compatibility mode:

  • Years 0-29 → 2000-2029
  • Years 30-99 → 1930-1999

=DATE(29, 5, 1) = May 1, 2029. =DATE(30, 5, 1) = May 1, 1930. Always use 4-digit years.

DATE vs. DATEVALUE

FunctionInputUse for
DATE()Three numbers (year, month, day)Building a date from numeric parts
DATEVALUE()Text string ("2026-09-06")Converting text that looks like a date

Common pitfalls

Displaying as a serial number

If DATE returns something like 46271 instead of a date, the cell is formatted as Number or General. Right-click → Format Cells → Date to fix.

Text in the arguments

DATE requires numeric inputs. Text arguments like DATE("2026", "9", "6") usually work due to auto-conversion but can fail if the text has spaces or non-numeric characters. Use VALUE() to be explicit: DATE(VALUE(A1), VALUE(B1), VALUE(C1)).

Negative year

Excel only supports dates from Jan 1, 1900 to Dec 31, 9999. =DATE(-1, 1, 1) returns #NUM!.

Overflow for month-end math

The DATE(year, month+1, 0) pattern is the classic pre-EOMONTH trick for last day of month. Still useful when you need custom logic (e.g., fiscal month-ends).

DATE in Google Sheets

Identical function in Google Sheets. Same syntax, same overflow behavior, same 2-digit-year quirk. Sheets uses the same 1900 date system as Excel.

Related functions

Related

TODAY

Returns today's date. No arguments.

Related

EOMONTH

Last day of the month N months away.

Related

EDATE

Adds N whole months to a date.

Related

YEAR / MONTH / DAY

Extract components from a date.

Excel Wizard

Date construction, automated

Building a date from disparate columns or text strings requires knowing DATE, DATEVALUE, LEFT/MID/RIGHT, and the overflow trick. Excel Wizard reads your data ("column A has YYYYMMDD text — convert to real dates") and writes the DATE-based formula for you.

Install Excel Wizard →

Frequently asked questions

What does the DATE function do in Excel?

DATE constructs a date from three separate numeric inputs — year, month, and day. Syntax: =DATE(year, month, day).

Does DATE handle month overflow?

Yes. =DATE(2026, 13, 1) becomes January 1, 2027. =DATE(2026, 0, 15) becomes December 15, 2025. Same with days.

How do I convert text like '20260906' into a real date?

Use DATE with LEFT, MID, RIGHT: =DATE(LEFT(A1,4), MID(A1,5,2), RIGHT(A1,2)).

What's the difference between DATE and DATEVALUE?

DATE builds from three numbers. DATEVALUE converts a text string that looks like a date into a real date. Use each based on what you have.