Excel DATEDIF Function

DATEDIF calculates the difference between two dates in years, months, or days, based on a unit code you specify. It returns a single number, not a date. The catch: DATEDIF works correctly and has for decades, but Microsoft never added it to the Insert Function list, so it won't autocomplete and Excel won't help you fill in the arguments.

Syntax

=DATEDIF(start_date, end_date, unit)

Parameter	Required	Description
`start_date`	Yes	The earlier of the two dates. Can be a date value or a cell reference.
`end_date`	Yes	The later of the two dates. If it falls before `start_date`, the formula returns `#NUM!`.
`unit`	Yes	A text code in quotes telling DATEDIF what to return: complete years, complete months, total days, or one of three partial-interval codes.

Basic Example

Say you're tracking employee tenure and need each person's exact years of service based on their hire date.

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

// B2       = the employee's hire date
// TODAY()  = the end date, always the current date
// "y"      = return complete years only

If B2 holds 3/15/2019 and today is 7/13/2026, this returns 7. Not 7.3, not 7.4. DATEDIF with "y" only counts full years that have actually elapsed; the 4 remaining months don't round up.

How DATEDIF Works

DATEDIF recognizes six unit codes

Each code changes what the result measures.

Code	Returns
`"Y"`	Complete years between the two dates
`"M"`	Complete months between the two dates
`"D"`	Total days between the two dates
`"YM"`	Months remaining after subtracting complete years
`"MD"`	Days remaining after subtracting complete years and months
`"YD"`	Days remaining after subtracting complete years, ignoring the month and year

DATEDIF truncates, it never rounds

"y" and "m" only count whole, completed periods. A gap of 4 years and 11 months returns 4 for "y", not 5. This trips up anyone expecting standard rounding. There's no fifth argument to change this behavior; if you need rounding, build it separately with ROUND or CEILING.

start_date must come before end_date

Feed DATEDIF an end date that's earlier than the start date and you get #NUM!, not a negative number. This is a strict requirement, not a suggestion. If you're not sure which of two dates is earlier, wrap both in MIN and MAX before passing them in.

⚠

The "MD" unit has a documented calculation bug. It can return a negative number, a zero, or a result that's simply wrong for certain date pairs, particularly when the days span months of different lengths. Don't use "MD" in anything that needs to be reliable. See the workaround below.

DATEDIF works, but Excel hides it

It's undocumented. Type =DATEDIF( into a cell and Excel shows no argument tooltip, no autocomplete dropdown, nothing. Pressing F1 for help won't find it either. None of that means the function is broken; it's been calculating correctly since Excel 2000. You just have to type the whole thing from memory.

Common Use Cases

Calculating exact age from a birthdate

A birthdate in one column, today's date as the reference point.

=DATEDIF(A2, TODAY(), "y")   // A2 = date of birth, returns complete years old

This is the single most common DATEDIF use. Since it truncates, someone born 32 years and 11 months ago still shows as 32, which matches how people actually state their age.

Building a full elapsed-time string

Sometimes a single number isn't enough. Combine three DATEDIF calls to get a human-readable breakdown.

=DATEDIF(B5,C5,"y") & " years, " & DATEDIF(B5,C5,"ym") & " months, " & DATEDIF(B5,C5,"md") & " days"

// "y"  = complete years between B5 and C5
// "ym" = leftover months after removing complete years
// "md" = leftover days after removing complete years and months

This returns something like "3 years, 4 months, 12 days." Because it relies on "md", treat the day count as approximate for edge-case date pairs. See the gotcha below for a safer alternative.

Flagging contracts approaching expiration

For a contract tracker, count months remaining instead of months elapsed by swapping which date goes first.

=DATEDIF(TODAY(), D2, "m")   // D2 = contract end date, returns months left before expiration

Combine this with conditional formatting, highlighting any row where the result drops below 2, and you've built a renewal alert without a single VBA line.

Handling Errors

DATEDIF throws two catchable errors: #NUM! and #VALUE!.

Common causes:

start_date falls after end_date (returns #NUM!)
unit isn't one of the six recognized codes, like typing "YY" instead of "Y" (returns #NUM!)
Either date argument isn't a real date, such as a text string Excel can't parse (returns #VALUE!)

A blanket IFERROR around DATEDIF hides which of these happened. A swapped date order and a genuinely broken date value both get the same generic message, which makes bad data harder to catch. Check for the date-order problem separately, then let IFERROR catch only the remaining case:

=IF(C2 < B2, "End date before start date", IFERROR(DATEDIF(B2, C2, "y"), "Invalid date"))

This flags a reversed date order with a message that actually says so, and reserves the generic fallback for real data problems, like text that isn't a valid date.

✓

If you're not sure which date comes first, wrap both arguments: =DATEDIF(MIN(B2,C2), MAX(B2,C2), "y"). This eliminates the #NUM! error entirely when the order isn't guaranteed.

Notes & Gotchas

What is the DATEDIF function in Excel?

DATEDIF calculates the difference between two dates and returns it as a single number, in years, months, or days depending on the unit code you supply. It's most often used for age calculations, tenure tracking, and elapsed-time reporting.

Why can't I find DATEDIF in the Excel function list? Is it a hidden function?

Yes. Microsoft has never added DATEDIF to the Insert Function dialog or the formula autocomplete list, even though it's been available in every Excel version since 2000. There's no official explanation for why. You have to type the formula manually; once you do, it calculates correctly and behaves like any other function.

How do you use DATEDIF to calculate the difference between two dates in years, months, and days?

Pass a start date, an end date, and a unit code, in that order. =DATEDIF(B2, C2, "y") returns complete years, "m" returns complete months, and "d" returns total days. Combine multiple unit codes in one concatenated formula to get a full "years, months, days" breakdown.

Why does DATEDIF give incorrect or negative results with the "MD" unit?

The "MD" code subtracts day-of-month numbers directly without properly accounting for how many days are in the months between the two dates. That produces a negative number, a zero, or a plainly wrong result for certain date pairs, most often when the end date's day number is smaller than the start date's. Of the six unit codes, only "D" (total days) is reliable across every possible date pair.

✓

To avoid the "MD" bug in an elapsed-time formula, calculate the remaining days with date arithmetic instead: =C2 - DATE(YEAR(C2), MONTH(C2) - DATEDIF(B2,C2,"m"), DAY(B2)). This subtracts the exact number of complete months first, then finds the true day remainder, sidestepping the bug entirely.

What happens if the start date is later than the end date?

DATEDIF returns #NUM!. It won't automatically flip the dates or return a negative difference like simple subtraction would. If your data source doesn't guarantee date order, wrap both arguments in MIN and MAX, or add an IF check before the DATEDIF call.

Does DATEDIF account for leap years correctly?

Yes. Because DATEDIF works off actual calendar dates rather than a fixed 365-day assumption, it correctly counts complete years and months across leap years without any extra adjustment. This matters for age and tenure calculations spanning multiple decades, where a fixed-day-count formula would slowly drift off by a day or more.

Related Functions

Function	Use this when...