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.
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.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Employee | Team | Hired | Years | As of | |
| 2 | Priya Nair | Finance | 4/14/2019 | 7 | 6/30/2026 | |
| 3 | Owen Castillo | Support | 9/8/2021 | |||
| 4 | Mei Tanaka | Sales | 6/30/2023 | |||
| 5 | Luis Herrera | Support | 11/17/2025 | |||
| 6 | Hannah Brooks | Finance | 1/20/2026 |
"Y" to "M" for complete months (86), or to "D" for days (2634).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:
"Y": 7 complete years."M": 86 complete months."D": 2634 days, the same as=F2-C2."YM": 2, the complete months left over after the 7 years.
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:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Employee | Team | Hired | Years | As of | |
| 2 | Priya Nair | Finance | 4/14/2019 | #NUM! | 6/30/2026 | |
| 3 | Owen Castillo | Support | 9/8/2021 | |||
| 4 | Mei Tanaka | Sales | 6/30/2023 | |||
| 5 | Luis Herrera | Support | 11/17/2025 | |||
| 6 | Hannah Brooks | Finance | 1/20/2026 |
=DATEDIF(C2,F2,"Y").Years and months together. Two DATEDIFs joined with & give a result like "4 years, 9 months". The second one uses "YM", not "M":
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Employee | Team | Hired | Service | As of | |
| 2 | Priya Nair | Finance | 4/14/2019 | 6/30/2026 | ||
| 3 | Owen Castillo | Support | 9/8/2021 | 4 years, 9 months | ||
| 4 | Mei Tanaka | Sales | 6/30/2023 | |||
| 5 | Luis Herrera | Support | 11/17/2025 | |||
| 6 | Hannah Brooks | Finance | 1/20/2026 |
"YM" is the number of complete months after the last anniversary: 9. Change it to "M" and the text reads 4 years, 57 months, because "M" counts every month since the hire date."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.
Owen Castillo was hired on 9/8/2021. In D3, return his complete years of service as of the date in F2.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Employee | Team | Hired | Years | As of | |
| 2 | Priya Nair | Finance | 4/14/2019 | 6/30/2026 | ||
| 3 | Owen Castillo | Support | 9/8/2021 | |||
| 4 | Mei Tanaka | Sales | 6/30/2023 | |||
| 5 | Luis Herrera | Support | 11/17/2025 | |||
| 6 | Hannah Brooks | Finance | 1/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.
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.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Employee | Team | Hired | Months | As of | |
| 2 | Priya Nair | Finance | 4/14/2019 | 6/30/2026 | ||
| 3 | Owen Castillo | Support | 9/8/2021 | |||
| 4 | Mei Tanaka | Sales | 6/30/2023 | |||
| 5 | Luis Herrera | Support | 11/17/2025 | |||
| 6 | Hannah Brooks | Finance | 1/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.
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.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Employee | Team | Hired | Service | As of | |
| 2 | Priya Nair | Finance | 4/14/2019 | 6/30/2026 | ||
| 3 | Owen Castillo | Support | 9/8/2021 | |||
| 4 | Mei Tanaka | Sales | 6/30/2023 | |||
| 5 | Luis Herrera | Support | 11/17/2025 | |||
| 6 | Hannah Brooks | Finance | 1/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.