Excel DATEDIF function: years, months and days between dates
DATEDIF counts the full years, months or days between two dates. =DATEDIF(H2,$K$6,"Y") returns Dana's full years of service from her hire date to the report date. The start date always comes first.
In the sample data, column H holds hire dates and K6 holds the report date, June 30, 2026. Dana was hired March 12, 2018.
Syntax
=DATEDIF(start_date, end_date, unit)| Unit | Returns |
|---|---|
| "Y" | Full years |
| "M" | Full months |
| "D" | Days |
| "YM" | Months left over after full years |
| "MD" | Days left over after full months. Microsoft warns this one can give wrong results. |
Examples
=DATEDIF(H2,$K$6,"Y")returns 8. Dana has 8 full years.=DATEDIF(H2,$K$6,"Y")&" yrs "&DATEDIF(H2,$K$6,"YM")&" mos"returns 8 yrs 3 mos.- For age, use today:
=DATEDIF(birthdate,TODAY(),"Y").
Why you can't find it
DATEDIF is an older function Excel keeps for compatibility. It doesn't show up in autocomplete or the function list, but it works when you type it. Google Sheets lists it normally.
Common mistakes
- Putting the later date first. DATEDIF returns #NUM!.
- Forgetting $ on the report date before copying down.
- Subtracting years with YEAR(K6)-YEAR(H2). That ignores the month and counts people as a year older than they are.
Other useful date functions
YEAR(H2) returns 2018. EOMONTH(H2,0) returns the last day of that month. To count hires before 2020, use =COUNTIF(H2:H7,"<"&DATE(2020,1,1)), which returns 3.
Try it: Years of service
K6 holds the report date. In C2, show how many full years Dana has worked here as of that date.
Graded live, like a real Excel test. Press New numbers to prove you didn't memorize the answer. More practice
Questions people ask
Why doesn't DATEDIF show up when I type it?
Excel hides it from autocomplete because it's kept for compatibility with older spreadsheets. Type the full formula and it works.
How do I calculate years of service in Excel?
Use =DATEDIF(hire_date,TODAY(),"Y"), or point to a fixed report date instead of TODAY().
Practice on a real spreadsheet
CellReady grades your formula the moment you press Enter and tells you why a wrong answer is wrong. The first 7 lessons are free, with no sign-up to start.
Related guides
What the $ sign does in Excel formulas, the difference between A1, $A$1, $A1 and A$1, the F4 shortcut, and a live exercise.
How to use COUNTIF in Excel with numbers, text and cell references, plus COUNTIFS for two conditions and a live exercise.
A one-page cheat sheet of the Excel formulas that show up on pre-employment tests, with the syntax, a real example and the mistake to avoid for each.