Excel practice › Excel functions › DATEDIF

DATEDIF in Excel: years, months and days between two dates

DATEDIF returns the time between two dates in complete years, complete months or days, depending on a unit you give in quotes. With a hire date in C2 and the date to count up to in F2, =DATEDIF(C2,F2,"Y") returns the complete years of service.

The earlier date goes first. And one caution from Microsoft's own page: Excel keeps the function for compatibility with old Lotus 1-2-3 workbooks, and one of its units, "MD", is known to give wrong results.

Skip to the 3 exercises ↓

Syntax

=DATEDIF(start_date, end_date, unit)

start_date
The first, earlier date.
end_date
The last, later date. If start_date is the later one, the result is #NUM!.
unit
What to count, in quotes: "Y" complete years, "M" complete months, "D" days. Also "YM" (months, ignoring days and years), "YD" (days, ignoring years) and "MD" (days, ignoring months and years), which Microsoft advises against.

In a real file the end date is usually TODAY(). On this page it's a fixed date in F2, so the answers don't change from one day to the next.

Live exampleComplete years of service
D2fx
ABCDEF
1EmployeeTeamHiredYearsAs of
2Priya NairFinance4/14/201976/30/2026
3Owen CastilloSupport9/8/2021
4Mei TanakaSales6/30/2023
5Luis HerreraSupport11/17/2025
6Hannah BrooksFinance1/20/2026

What "complete" means

DATEDIF counts only the periods that have fully passed. Owen Castillo was hired on 9/8/2021. On 6/30/2026 the two year numbers are 5 apart, but his fifth anniversary is in September, so =DATEDIF(C3,F2,"Y") is 4.

That's why subtracting the years is not the same thing: =YEAR(F2)-YEAR(C3) says 5, one too many for everyone whose anniversary hasn't come yet this year.

The units, for 4/14/2019 to 6/30/2026:

Where it goes wrong

The dates are the wrong way round. A subtraction puts the later date first (=F2-C2). DATEDIF wants the earlier date first, and answers with an error otherwise:

Live example#NUM!: the later date comes first
D2fx
ABCDEF
1EmployeeTeamHiredYearsAs of
2Priya NairFinance4/14/2019#NUM!6/30/2026
3Owen CastilloSupport9/8/2021
4Mei TanakaSales6/30/2023
5Luis HerreraSupport11/17/2025
6Hannah BrooksFinance1/20/2026

Years and months together. Two DATEDIFs joined with & give a result like "4 years, 9 months". The second one uses "YM", not "M":

Live exampleYears and months in one cell
D3fx
ABCDEF
1EmployeeTeamHiredServiceAs of
2Priya NairFinance4/14/20196/30/2026
3Owen CastilloSupport9/8/20214 years, 9 months
4Mei TanakaSales6/30/2023
5Luis HerreraSupport11/17/2025
6Hannah BrooksFinance1/20/2026

"MD" and what Microsoft says about it

The natural next step is the leftover days: 4 years, 9 months and so many days, with the unit "MD". Microsoft's page says not to. It does not recommend "MD" and lists as a known issue that it can return a negative number, a zero or an inaccurate result.

For the days after the last complete month, the same page shows another route: the end date minus the first day of its month, built with DATE, as in =F2-DATE(YEAR(F2),MONTH(F2),1). That counts from the 1st of the month, so check that it's the number you mean before you use it.

More generally, Microsoft describes DATEDIF as a function Excel provides "to support older workbooks from Lotus 1-2-3" and warns that it may calculate incorrect results in certain scenarios. For plain days between two dates you don't need it: subtract, =F2-C2.

The sheets on this page don't reproduce Excel's "MD" behavior, so there is no live example of it here.

Practice: 3 exercises

Type the formula in the box, press Enter. Clicking a cell inserts its address. Each answer is checked on the data you see and again on changed data, so a typed-in value won't pass.

Exercise 1Complete years of service

Owen Castillo was hired on 9/8/2021. In D3, return his complete years of service as of the date in F2.

D3fx
ABCDEF
1EmployeeTeamHiredYearsAs of
2Priya NairFinance4/14/20196/30/2026
3Owen CastilloSupport9/8/2021
4Mei TanakaSales6/30/2023
5Luis HerreraSupport11/17/2025
6Hannah BrooksFinance1/20/2026
Show the answer

=DATEDIF(C3,F2,"Y") gives 4.

From 9/8/2021 to 6/30/2026 four anniversaries have passed; the fifth is in September. =YEAR(F2)-YEAR(C3) would say 5.

Exercise 2Months on the job

New hires finish probation after 6 complete months. Hannah Brooks was hired on 1/20/2026. In D6, return how many complete months she has worked as of the date in F2.

D6fx
ABCDEF
1EmployeeTeamHiredMonthsAs of
2Priya NairFinance4/14/20196/30/2026
3Owen CastilloSupport9/8/2021
4Mei TanakaSales6/30/2023
5Luis HerreraSupport11/17/2025
6Hannah BrooksFinance1/20/2026
Show the answer

=DATEDIF(C6,F2,"M") gives 5.

Five complete months have passed, from January 20 to June 20. The sixth is complete on July 20, so on June 30 she is still on probation.

Exercise 3Years and months of service

For a service letter: in D2, build the text 7 years, 2 months for Priya Nair, from her hire date in C2 and the date in F2.

D2fx
ABCDEF
1EmployeeTeamHiredServiceAs of
2Priya NairFinance4/14/20196/30/2026
3Owen CastilloSupport9/8/2021
4Mei TanakaSales6/30/2023
5Luis HerreraSupport11/17/2025
6Hannah BrooksFinance1/20/2026
Show the answer

=DATEDIF(C2,F2,"Y")&" years, "&DATEDIF(C2,F2,"YM")&" months" gives 7 years, 2 months.

The first DATEDIF counts the complete years, the second the complete months after them, and & joins both with the words. With "M" the second number would be 86.

Questions people ask

Is DATEDIF still supported in Excel?

Yes, it works, and Microsoft documents it. Its page describes it as kept for older workbooks that came from Lotus 1-2-3 and warns that it can calculate incorrect results in certain scenarios; the known issue it lists is the "MD" unit. It lists none for "Y", "M" or "D".

How do I calculate age from a date of birth in Excel?

Count the complete years up to today: =DATEDIF(A2,TODAY(),"Y"), with the date of birth in A2. Microsoft's page names age as a typical use of DATEDIF. The sheets on this page have no TODAY, so the exercises use a fixed date in a cell.

Why does DATEDIF return #NUM!?

Because the start date is later than the end date. DATEDIF needs the earlier date as its first argument. Swap the two dates.

How do I count the days between two dates without DATEDIF?

Subtract the earlier date from the later one: =B2-A2. Microsoft's DATEDIF page gives this tip itself. =DAYS(B2,A2) does the same, with the end date first.

Keep going

Lesson 7 is seven drills on a design studio's invoices: days between two dates, month-end due dates with EOMONTH, business days with NETWORKDAYS, and TEXT for month labels and amounts inside a sentence. It's part of Pro ($29 once for all three tracks, or $19 for the Excel track alone). Lessons 1 and 2 and the skills test are free.

Related

EOMONTH function, with live examplesNETWORKDAYS function, with live examplesTEXT function, with live examplesCONCAT function and the & operator, with live examplesExcel date functions practice (Pro)Excel functions with live examplesExcel practice online

Written and tested by Nico, who builds SheetDrill (about). Every formula on this page ran through the engine that checks the exercises before the page was published. Checked against: Microsoft Support: DATEDIF function, DAYS function. Last checked November 2026.