The DATEDIF guide
=DATEDIF(B2, $H$1, "y") counts the whole years from the date in B2 to the date in H1: an age, or years of service. "ym" gives the months on top of those years.
The syntax
=DATEDIF(start_date, end_date, unit)
The earlier date goes first; the other way round returns #NUM!. The unit goes in quotes. Excel doesn't suggest DATEDIF as you type, but it works in every version.
| Unit | Returns |
|---|---|
| "y" | Whole years |
| "m" | Whole months |
| "d" | Days |
| "ym" | Months left after the whole years |
| "yd" | Days left after the whole years |
| "md" | Days left after the whole months |
Why not YEAR minus YEAR?
Someone born on 10/12/1990 is 35 on 6/30/2026, not 36: their birthday that year hasn't come yet. YEAR(H1)-YEAR(B2) counts it anyway. DATEDIF with "y" only counts a year once it is complete.
Why not divide by 365?
Leap years add a day about every four years. Over 39 years that is ten days, so INT((H1-B2)/365) makes someone a year older up to ten days before their birthday. Dividing by 365.25 is closer but still an estimate. DATEDIF counts calendar years.
Years and months
Service of 7 years and 3 months is DATEDIF(C2, $H$1, "y") and DATEDIF(C2, $H$1, "ym"). The "m" unit gives all 87 months instead. As one label: =DATEDIF(C2,$H$1,"y")&" years, "&DATEDIF(C2,$H$1,"ym")&" months".
The "md" warning
Microsoft's own page warns that "md" can return a negative number, a zero or a wrong result. From 1/31 to 3/1 in a year without a leap day it returns -2. For days after the last whole month, subtract instead: =H1-EDATE(C2, DATEDIF(C2, H1, "m")). "m" and "ym" can also count one month short when the start date is the 31st and the end month is shorter.
Today or a fixed date?
DATEDIF(B2, TODAY(), "y") gives an age that updates every day. A report as of a date needs that date in a cell, so it gives the same answer next week.
Common mistakes
- The dates the wrong way round. #NUM!.
- The unit without quotes.
DATEDIF(B2, H1, y)returns #NAME?. - "m" instead of "ym". All the months, not the ones left over.
FAQ
- How do I calculate age from date of birth in Excel?
- Use =DATEDIF(B2,TODAY(),"y") for the age today, or put a fixed date in a cell and use it instead of TODAY() for a report.
- Why is (TODAY()-birthdate)/365 wrong?
- It ignores leap days, so it can make someone a year older a few days before their birthday. DATEDIF with "y" counts whole calendar years.
- How do I show years and months of service in Excel?
- Years: =DATEDIF(C2,end,"y"). Months on top: =DATEDIF(C2,end,"ym"). Join them with & for a label.
- Why does DATEDIF return #NUM!?
- The start date is later than the end date. DATEDIF needs the earlier date first.



































































