DATEDIF in Excel

Q: How do I calculate an age in Excel?

Use =DATEDIF(B2,C2,"Y"), with the date of birth in B2 and the date of the age in C2. Add DATEDIF(B2,C2,"YM") for the months left over.

Q: Can I use TODAY() as the end date?

Yes: =DATEDIF(B2,TODAY(),"Y") gives an age that updates every day. For a report that must not change, put a fixed date in a cell and lock it with $F$1.

Last updated October 30, 2026

DATEDIF counts the full years, months or days between two dates. For example, =DATEDIF(B2,C2,"Y") returns 9 for someone who started on September 14, 2015 and left on March 31, 2025. The start date comes first, and the unit goes in quotes.

DATEDIF syntax

=DATEDIF(start_date, end_date, unit)

start_date: The earlier date: a cell such as B2, or DATE(2015,9,14). It must not be after end_date.
end_date: The later date, for example a deadline or a fixed reference date in a locked cell like $F$1.
unit: What to count, in quotes: "Y" full years, "M" full months, "D" days, "YM" months left after the full years, "MD" days left after the full months, "YD" days left after the full years.

Practice DATEDIF here

Type your formula straight into the sheet. It’s checked like real Excel, and you can write it with commas or semicolons.

Exercise 1 of 1

Count full years of service

Emma started on the date in B2 and left on the date in C2. In D2, calculate her full years of service with DATEDIF.

Employee	Start date	End date	Full years
Emma Clarke	14/09/2015	31/03/2025

How to use DATEDIF, step by step

Click the cell for the result and type =DATEDIF( in full: Excel doesn’t suggest it in the function list, and shows no argument tooltip for it.
Select the start date, type a comma, then select the end date.
Type a comma and the unit in quotes: "Y" for years, "M" for months, "D" for days.
Close the parenthesis and press Enter. DATEDIF counts complete units only and never rounds up.

DATEDIF examples

Full years of service

=DATEDIF(B2,C2,"Y")

From September 14, 2015 to March 31, 2025: 9 full years.

Full months of a contract

=DATEDIF(B2,C2,"M")

From February 1, 2023 to January 31, 2025: 23 full months.

Days until a deadline

=DATEDIF(B2,C2,"D")

From February 3 to June 18, 2025: 135 days.

Age in years and months

=DATEDIF(B2,C2,"Y")&" years "&DATEDIF(B2,C2,"YM")&" months"

Born July 22, 1990, on March 15, 2025: 34 years 7 months.

Common DATEDIF errors and how to fix them

Error	Cause	Fix
#NUM!	The start date is after the end date, for example when the two cells are swapped.	Put the earlier date first: `=DATEDIF(B2,C2,"Y")`, with B2 before C2.
#NUM! (unit)	The unit isn’t one DATEDIF knows, for example "YY" or "W" for weeks.	Use "Y", "M", "D", "YM", "MD" or "YD". For weeks, divide the days by 7: `=DATEDIF(B2,C2,"D")/7`.
#VALUE!	One of the dates is text that Excel can’t read as a date, for example a date pasted from another system.	Convert the cell to a real date (it aligns to the right), or build it with `DATE(year,month,day)`.
#NAME?	The unit has no quotes (`Y` instead of `"Y"`) or the function name is misspelled.	Put the unit between straight quotes and type the name in full: DATEDIF.

DATEDIF vs subtracting dates and YEARFRAC

Subtracting two dates (=C2-B2) gives the days between them, like DATEDIF with "D", but the result cell may take a date format and show a date instead of a number. Dividing those days by 365 gives decimals and drifts with leap years. YEARFRAC returns the fraction of a year between two dates (about 9.5 years, for example), which suits interest or pro-rata calculations. DATEDIF returns whole, complete units, which is what you need for an age, years of service or the months of a contract. Microsoft keeps DATEDIF for compatibility and warns that "MD" can return wrong results around the end of a month.

Frequently asked questions

Why can’t I find DATEDIF in Excel?

DATEDIF isn’t listed in the function suggestions or in the Insert Function dialog, but it works in current versions of Excel. Type =DATEDIF( in full and Excel accepts it.

How do I calculate an age in Excel?

Use =DATEDIF(B2,C2,"Y"), with the date of birth in B2 and the date of the age in C2. Add DATEDIF(B2,C2,"YM") for the months left over.

Can I use TODAY() as the end date?

Yes: =DATEDIF(B2,TODAY(),"Y") gives an age that updates every day. For a report that must not change, put a fixed date in a cell and lock it with $F$1.

Why does DATEDIF return #NUM!?

Almost always because the start date is after the end date. DATEDIF doesn’t return negative results: swap the two dates, or check that both cells hold real dates.

Keep practising

All 5 DATEDIF exercises, with explanations and progress tracking.

Open the DATEDIF practice set