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
| Argument | Type | Description |
|---|---|---|
year Required | Number | Full 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 Required | Number | 1-12 for Jan-Dec. Values outside overflow: 13 = next January, 0 = last December, -1 = last November. |
day Required | Number | 1-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)
From separate columns
=DATE(A1, B1, C1)
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)
Month = 0 → last December
=DATE(2026, 0, 15)
Day = 32 → first of next month
=DATE(2026, 1, 32)
Day = 0 → last day of prior month
=DATE(2026, 3, 0)
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))
2. Last day of the current month
Month-end date
=DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 0)
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)
4. First Monday of a month
Combine DATE with WEEKDAY
=DATE(2026, 9, 1) + MOD(2 - WEEKDAY(DATE(2026, 9, 1), 2), 7)
5. Nth day of month (birthday reminder pattern)
15th of every month, this year
=DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 15)
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
| Function | Input | Use 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
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.
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)).
Excel only supports dates from Jan 1, 1900 to Dec 31, 9999. =DATE(-1, 1, 1) returns #NUM!.
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
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.