DATEDIF Function

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.

DATEDIF is especially useful when the answer needs to be expressed the way people talk about time rather than as one raw serial difference. That makes it helpful for ages, service length, subscription terms, and project durations where whole months or years matter more than just the total day count.

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

DATEDIF is one of the most reliable ways to measure elapsed time between two dates when you care about full units instead of rough differences. It counts only completed years, months, or days, so partial intervals are not rounded up early.

That makes it especially useful in HR, subscriptions, and reporting work where an anniversary must actually be reached before Excel counts the next year or month.

• 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

The most basic use of DATEDIF is counting how many full years have passed between the start date and end date.

=DATEDIF(B1,B2,"Y")

This returns the number of completed years only, which is useful for age, tenure, and anniversary-based logic.

Example 2 - Total Full Months

If you need the total number of whole months in the span, the "M" unit is the right choice.

=DATEDIF(B1,B2,"M")

This is useful for subscriptions, contracts, and other month-based reporting.

Example 3 - Leftover Months After Full Years

One of the most useful DATEDIF patterns is returning only the remaining months after full years have already been counted.

=DATEDIF(B1,B2,"YM")

This is useful when you want a result like "5 years, 3 months" instead of one long month count.

Example 4 - Completed Years to Today

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

=DATEDIF(B1,TODAY(),"Y")

This is a common pattern for age and seniority calculations that need to stay current.

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.