Home › Excel functions › DATE

DATE Function in Excel: Build and Fix Dates

DATE builds a real Excel date from a year, month and day. It's how you turn separate numbers or messy imported text into dates that sort, filter and calculate correctly.

Excel CSE · 2 min read

DATE syntax

=DATE(2026, 11, 12) returns November 12, 2026. Always use a four-digit year: two-digit years are interpreted inconsistently.

Months and days that "overflow"

DATE rolls over automatically, which is very handy:

• =DATE(2026, 13, 1) returns January 1, 2027.
• =DATE(2026, 3, 0) returns February 28, 2026: day 0 is the last day of the previous month.
• =DATE(2026, 1, 35) returns February 4, 2026.

Take a date apart: YEAR, MONTH and DAY

With a date in A2:

• =YEAR(A2), =MONTH(A2) and =DAY(A2) return the parts as numbers.
• First day of the month: =DATE(YEAR(A2), MONTH(A2), 1)
• Same date next year: =DATE(YEAR(A2)+1, MONTH(A2), DAY(A2))
• Add 3 months: =EDATE(A2, 3) is simpler, and handles month ends correctly. See also EOMONTH.

Convert text to real dates

Imported dates often arrive as text, like "20261112" or "12-11-2026". You can tell because they're left-aligned and don't sort correctly. Rebuild them with DATE and the LEFT, MID and RIGHT functions:

Both return November 12, 2026 as a real date. For a whole column, Text to Columns with the column format set to Date (and the right order, such as DMY) is often quicker.

Dates inside other formulas

Typing a date as text inside a formula depends on regional settings. DATE avoids that:

See SUMIFS and COUNTIFS.