Excel's DATEDIF function returns the gap between two dates in years, days or other units. This guide covers the syntax, Microsoft's own worked examples, and the one case where you are better off not using DATEDIF at all.
The DATEDIF syntax
Microsoft's DATEDIF help page gives the syntax as:
DATEDIF(start_date,end_date,unit)
- start_date is the earlier date.
- end_date is the later date.
- unit is a code in double quotes that tells Excel what to count, such as "Y" or "D".
DATEDIF is an odd one. Microsoft says Excel provides it to support older workbooks from Lotus 1-2-3, and that it can give incorrect results in some scenarios. It still works, but treat it as a legacy function.
The unit codes
DATEDIF accepts six unit codes. The table below gives only what Microsoft's page confirms for each one. Where a row says to check Microsoft's page, this article does not describe that code.
| Code | What it returns |
|---|---|
| "Y" | Years. Microsoft's example: 1/1/2001 to 1/1/2003 gives 2 |
| "D" | Days. Microsoft's example: 6/1/2001 to 8/15/2002 gives 440 |
| "YD" | Days, ignoring years. Microsoft's example: 6/1/2001 to 8/15/2002 gives 75 |
| "YM" | Listed by Microsoft as ignoring years. Check Microsoft's page for details |
| "M" | Check Microsoft's page |
| "MD" | Check Microsoft's page |
For the common jobs, counting years or days, "Y" and "D" are all you need.
Calculate the difference between two dates
- Put the start date in one cell, for example A2.
- Put the end date in another cell, for example B2.
- In a third cell, type the formula with the unit you want:
=DATEDIF(A2,B2,"Y")
- Press Enter.
Keep the start date first and the end date second, matching the order in the syntax.
What the unit codes return in Microsoft's examples
Microsoft's page works through three examples:
| Formula | Result |
|---|---|
=DATEDIF(1/1/2001,1/1/2003,"Y") |
2 |
=DATEDIF(6/1/2001,8/15/2002,"D") |
440 |
=DATEDIF(6/1/2001,8/15/2002,"YD") |
75 |
The last two rows use the same dates. With "D" you get every day between them. With "YD" you get a much smaller number, because Microsoft lists the "YD" and "YM" units as ignoring years. That makes "YD" useful for questions like "how many days since the last anniversary of this date", and not useful when you want the total gap.
Use subtraction when you only need days
The most common mistake is reaching for DATEDIF when all you want is a count of days. Given Microsoft's warning about incorrect results in some scenarios, there is a simpler route.
=B2-A2
With the start date in A2 and the end date in B2, this returns the days between them with no unit code to get wrong. Save DATEDIF for when you need whole years with "Y" or days that ignore years with "YD".
Where to read more
Microsoft's page is the official reference. For worked examples from other writers, see Exceljet's DATEDIF page or Ablebits' guide to date differences. Excel's other date tools are listed in Microsoft's date and time functions reference.
