Excel DATE Function

DATE combines a year, month, and day number into a single value that Excel recognizes as a real date. You give it three numbers, and it hands back one serial value you can format, sort, or use in date math. The part that trips people up: DATE doesn't reject invalid month or day numbers. It rolls them over into the next or previous period without an error, which can produce dates you never intended.

Available in: Excel 365, Excel 2021, Excel 2019, Excel 2016, and earlier versions back to Excel 2007. DATE behaves identically across all of them, including the rollover behavior described below.

Syntax

=DATE(year, month, day)

Parameter	Required	Description
year	Yes	The year number. Values from 0 to 1899 get 1900 added to them automatically. Enter the full four-digit year to avoid this.
month	Yes	The month number. Accepts values outside 1-12; Excel rolls the date into an adjacent year to compensate.
day	Yes	The day number. Accepts values outside the valid range for that month; Excel rolls the date into an adjacent month to compensate.

Basic Example

Say you're cleaning up an import from an old accounting system that stores hire dates as three separate columns: Year, Month, and Day. Column C has the year, column D the month, column E the day.

=DATE(C2, D2, E2)

// C2 = year, for example 2023
// D2 = month, for example 6
// E2 = day, for example 14

With C2 as 2023, D2 as 6, and E2 as 14, this returns June 14, 2023 as a real Excel date. Once it's a true date value instead of three text fragments, you can sort it, subtract it from another date, or filter a pivot table by year.

How DATE Works

It returns a serial number, not text

Excel stores every date as a sequential number starting at 1 for January 1, 1900. DATE(2026, 7, 13) doesn't return the text "7/13/2026"; it returns the number 46216, which Excel then displays using whatever date format the cell has applied. If the cell is formatted as General, you'll see the raw number instead of a readable date.

Month and day arguments roll over automatically

This is the behavior most articles skip. DATE doesn't validate that month is between 1 and 12 or that day fits the month. Feed it DATE(2026, 0, 1) and it counts backward, returning December 1, 2025. Feed it DATE(2026, 4, 31) and since April has 30 days, it rolls forward to May 1, 2026. This rollover is genuinely useful once you understand it, and genuinely dangerous if you don't.

Year values under 1900 get shifted forward

If year falls between 0 and 1899, Excel adds 1900 to it before building the date. DATE(26, 7, 13) doesn't return the year 26. It returns July 13, 1926. This usually surfaces when a formula references a two-digit year typed by mistake, or when data was imported with a truncated year field.

It builds dates dynamically from other cells

The real value of DATE is assembling a date that changes when the inputs change. Instead of hardcoding a date, you can reference cells holding year, month, and day, or even derive those numbers with other formulas. This is what makes DATE useful in models where a fiscal year or reporting period shifts based on user input.

It works with negative numbers too

DATE(2026, 7, -5) is valid. A negative day rolls back from the first of the month by one more than its magnitude, so day -5 moves 6 days before July 1, 2026, returning June 25, 2026. The same logic applies to negative months. This is an edge case worth knowing, but rarely something you'd build into a formula on purpose.

Common Use Cases

Rebuilding dates from split columns

You're importing a CSV where year, month, and day arrived in separate fields instead of one date column.

=DATE(A2, B2, C2)   // combines split date parts into one real date

Shifting a date by a specific number of months

You need to calculate a date exactly 3 months after an invoice date in cell B2, accounting for different month lengths automatically.

=DATE(YEAR(B2), MONTH(B2)+3, DAY(B2))

// YEAR(B2)   = keeps the same year unless the month rollover pushes it forward
// MONTH(B2)+3 = adds 3 months; DATE handles the year rollover if needed
// DAY(B2)    = keeps the same day of month

Getting the first day of the current month

You want a formula that always returns the first of the current month, useful for month-to-date reports that refresh automatically.

=DATE(YEAR(TODAY()), MONTH(TODAY()), 1)   // returns the 1st of this month, every time

Building a fiscal year start date

Your fiscal year starts April 1. You want the fiscal year start for whatever year is in cell F2.

=DATE(F2, 4, 1)   // F2 = fiscal year number, returns April 1 of that year

Handling Errors

DATE rarely throws a visible error, which is part of why it's risky. It returns #VALUE! only when an argument can't be interpreted as a number at all, such as referencing a cell that contains plain text like "June" instead of the number 6.

Common causes of #VALUE!:

• A referenced cell contains text instead of a number
• An argument references an empty cell that was expected to hold a number, combined with other text in the same formula
• A cell contains a date stored as text rather than a numeric value

=IFERROR(DATE(A2, B2, C2), "Check date inputs")

// Catches #VALUE! only, such as A2, B2, or C2 holding text instead of a number.
// It does NOT catch rollover mistakes, like a day value of 32.
// A bad day or month still returns a valid-looking date, not an error.

Notes & Gotchas

What is the DATE function formula in Excel and how do I use it?

The formula is =DATE(year, month, day), and it returns a single date value built from those three numbers. Type the year as a full four-digit number, the month as 1 through 12, and the day as 1 through the number of days in that month. You can reference cells instead of typing numbers directly, which is what makes DATE useful for dynamic worksheets.

How do I add or subtract days, months, or years from a date in Excel?

For days, simple addition works: =A2+30 adds 30 days to the date in A2. For months and years, wrap DATE around YEAR, MONTH, and DAY so Excel handles the calendar math correctly: =DATE(YEAR(A2), MONTH(A2)+1, DAY(A2)) adds one month. Excel also has a dedicated EDATE function for month and year shifts, which is shorter to write and handles end-of-month edge cases more predictably.

How do you calculate the difference between two dates in Excel?

Subtracting two dates directly (=B2-A2) gives you the total number of days between them. For differences in complete years, months, or a mix of units, use DATEDIF(start_date, end_date, unit), which accepts unit codes like "y" for complete years and "m" for complete months. DATEDIF isn't built from DATE, but the two are frequently used together: DATE assembles the dates, DATEDIF measures the gap between them.

How do I convert a text value into a recognized date in Excel?

Use DATEVALUE(text) when your date is stored as one text string, such as "2026-07-13" imported from a system export. DATE is different: use it when your date arrives as three separate numeric parts (year, month, day in different cells) rather than one text string. If you're not sure which situation you're in, check whether the value is left-aligned in the cell; text-based dates left-align by default, while real dates right-align.

What are the main date and time functions available in Excel?

TODAY() returns the current date and updates automatically each day. NOW() does the same but includes the current time. EDATE(start_date, months) shifts a date forward or backward by whole months. WORKDAY(start_date, days) calculates a date a given number of business days out, skipping weekends. NETWORKDAYS(start_date, end_date) counts the business days between two dates. DATE differs from all of these: it's the one that builds a date from raw components rather than calculating from an existing date.

Why does DATE roll over into the next month or year instead of showing an error?

Excel treats month and day as arithmetic values, not fixed categories, so it counts past the valid range rather than rejecting it. DATE(2026, 14, 1) doesn't error; it counts 14 months from a zero point and lands on February 1, 2027. This is deliberate behavior, useful in formulas that add months, but it means a typo in your data won't necessarily produce a visible error. Always validate month and day inputs if they come from unreliable sources like imported files or manual entry.

Why does DATE show a number like 46216 instead of a readable date?

DATE always returns a serial number; the readable format is a display setting, not part of the formula's output. Select the cell, open Format Cells, and choose a date format. This commonly happens after copying a DATE formula into a cell that previously held General or Number formatting.

What happens if I leave one of the arguments blank or reference an empty cell?

An empty cell is treated as 0, not as an error. DATE(2026, 0, 0) doesn't fail; it returns November 30, 2025, because both the month and day roll backward from their zero points. This is one of the more common silent mistakes with DATE: a blank cell in a formula chain produces a wrong-but-valid-looking date instead of flagging the missing input.

Related Functions

Function	Use this when...
DATEDIF	You need the difference between two dates in complete years, months, or days.