FormuladrillDATE and DATEDIF
DATE(year, month, day) builds a real date from three numbers and handles overflow, so DATE(2026, 1, 45) is 14 February 2026. DATEDIF(start, end, unit) returns the complete years ("y"), months ("m") or days ("d") between two dates; it is undocumented in Excel but works in every version.
Syntax
DATE(year, month, day) DATEDIF(start_date, end_date, unit)| Part | What it is |
|---|---|
year, month, day | Numbers or cells. Months beyond 12 and days beyond the month roll over into the next period. |
start_date, end_date | Real dates, with end_date on or after start_date. |
unit | "y" complete years, "m" complete months, "d" days, "ym" months ignoring years, "md" days ignoring months and years, "yd" days ignoring years. |
Examples
=DATE(2026, 3, 15)Result: 15 March 2026 as a real date you can add days to
=DATEDIF(DATE(2020, 1, 15), DATE(2026, 11, 3), "y")Result: 6
Complete years, the way age is counted. With "m" the result is 81 months.
=DATEDIF(A2, TODAY(), "y") & " years, " & DATEDIF(A2, TODAY(), "ym") & " months"Result: 6 years, 9 months when A2 is 15 January 2020 and today is 3 November 2026
=DATE(YEAR(A2) + 1, MONTH(A2), DAY(A2))Result: The same day one year later
Overflow is handled: 29 Feb 2024 plus a year gives 1 Mar 2025.
Common mistakes
- #NUM! from DATEDIF: the start date is after the end date. Swap them or use ABS on a simple subtraction.
- DATEDIF does not appear in the formula autocomplete; it is there anyway, type it in full.
- Days between dates do not need DATEDIF: =B2 - A2 is enough when both cells hold real dates.
- Typing "2026-03-15" in quotes gives text, not a date, in some locales. Build it with DATE or DATEVALUE.
Practice DATE and DATEDIF
These drills check your answer on live data, free, no account needed.
- Days between datesWork out how many days each task took from start to end. Fill D2 and D3.
- Year and monthSplit the order date in A2 into its year (B2) and its month number (C2).
Questions
How do I calculate age in Excel?
=DATEDIF(birth_date, TODAY(), "y") gives age in complete years.
How do I count the months between two dates?
=DATEDIF(A2, B2, "m") for complete months. For a rough count that includes partial months use =(YEAR(B2) - YEAR(A2)) * 12 + MONTH(B2) - MONTH(A2).
How do I get the year and month from a date?
=YEAR(A2) and =MONTH(A2) return numbers. =TEXT(A2, "yyyy-mm") gives a sortable text label like 2026-11.
Related
- TODAY and NOWHow the Excel TODAY and NOW functions insert the current date or date and time, with formulas for days until a deadline, age, overdue flags and fixed dates.
- EOMONTH and EDATEHow the Excel EOMONTH function returns the last day of a month and EDATE adds or subtracts whole months, with examples for payment terms and first of month.
- NETWORKDAYS and WORKDAYHow NETWORKDAYS counts the working days between two dates and WORKDAY adds working days, skipping weekends and holidays, and .INTL for other weekends.
- TEXTHow the Excel TEXT function turns a number or date into text in a format you choose, with examples for dates, thousand separators, percentages and padding.