Summary

The DATEDIF function calculates the difference between two dates in completed years, months, or days. That word completed matters. DATEDIF does not estimate. It counts only full units that have actually been reached.

This is why DATEDIF is useful in age calculations, employee tenure, subscription length, and project tracking. If someone has not reached the next anniversary yet, DATEDIF will not count that next year or month early.

Syntax

=DATEDIF(start_date, end_date, "unit")

DATEDIF needs a start date, an end date, and a quoted unit code such as "y" or "m". One useful detail is that DATEDIF usually does not appear in Excel's autocomplete list, so you often need to type it manually.

The function also expects the start date to be earlier than the end date. If the dates are reversed, Excel can return a #NUM! error.

Arguments

• start_date - The earlier date where the interval begins.
• end_date - The later date where the interval ends.
• unit - A quoted code that tells Excel what kind of difference to return.

The most common unit codes are:

• "y" for completed years
• "m" for completed months
• "d" for total days
• "ym" for leftover months after full years
• "yd" for leftover days after full years
• "md" for leftover days after full months

DATEDIF vs Other Date Math

DATEDIF is not the only way to compare dates, so it helps to know why you would use it instead of direct subtraction or another date function.

The main distinction is that DATEDIF is designed for completed intervals, not approximate ones.

Using DATEDIF

The easiest way to understand DATEDIF is to think about anniversaries. If someone started on January 1, 2020 and today is December 31, 2025, they have not yet completed six full years. DATEDIF with the "y" unit will still return 5 until the next anniversary is reached.

This makes the function especially useful in HR and reporting work, where counting a full unit too early can cause a real logic problem.

• Use "y" when you need completed years.
• Use "m" when you need total full months.
• Use "ym" when you want leftover months after counting full years.

One caution is the "md" unit. It exists, but it is often treated carefully because it can be less intuitive in month-end situations. For high-stakes calculations, many people prefer to rely more on "y", "m", and "ym".

Example 1 - Completed Years

Use the "y" unit when you want the number of fully completed years between two dates.

=DATEDIF(A1,B1,"y")

This is useful for age, work tenure, or any calculation based on full anniversaries.

Example 2 - Total Full Months

Use the "m" unit when you want the total number of completed months in the entire span.

=DATEDIF(A1,B1,"m")

This is useful for subscriptions, contracts, and billing periods.

Example 3 - Leftover Months After Full Years

Use the "ym" unit when you want only the extra months after counting full years.

=DATEDIF(A1,B1,"ym")

This is useful when you want to write a result like "5 years, 3 months" instead of just total months.

Example 4 - Completed Years to Today

You can combine DATEDIF with TODAY() to create a live result that updates automatically.

=DATEDIF(A1,TODAY(),"y")

This is a common pattern for age and seniority calculations.

Conclusion Recap

DATEDIF is most useful when you care about completed time units, not rough date differences. Once you understand the unit codes and the idea of full anniversaries, it becomes much easier to choose the right interval for age, tenure, and reporting logic.

• Core job: DATEDIF returns completed years, months, or days between two dates.
• Main benefit: It counts full units only, which is useful for age and tenure logic.
• Useful units: "y", "m", and "ym" are the most common.
• Common caution: The "md" unit can be less reliable in some scenarios.
• Dynamic use: It combines well with TODAY() for live age or service calculations.