DATEDIF Excel Function: Syntax and Examples

By Dr. Zubair Khalid, DVM, MS, PhD · 2026-10-03

The DATEDIF Excel function returns the difference between two dates in whole days, months or years. It is the standard way to calculate age, tenure or elapsed time in a spreadsheet. The catch is that DATEDIF is a hidden function, so Excel will not show it in the formula autocomplete list.

Quick Answer

Syntax: =DATEDIF(start_date, end_date, unit)
The unit argument is a text code: "Y", "M", "D", "MD", "YM" or "YD"
"Y" returns complete years, "M" complete months, "D" total days
The result is always a whole number, and it is never negative
DATEDIF is not listed in Excel's function autocomplete, but it still works in every modern version

Syntax

Argument	Required?	Meaning
start_date	Required	The earlier date. Can be a cell reference, a DATE function result, or a date typed in quotes.
end_date	Required	The later date. Same input types as start_date.
unit	Required	A text code that sets the unit of the result. Must be in double quotes.

The unit codes behave as follows.

Unit	Returns
`"Y"`	Complete years between the two dates
`"M"`	Complete months between the two dates
`"D"`	Total days between the two dates
`"MD"`	Days, ignoring months and years
`"YM"`	Complete months, ignoring years
`"YD"`	Days, ignoring years

A basic formula looks like this.

=DATEDIF(A2, B2, "Y")

That returns the number of complete years between the date in A2 and the date in B2.

How It Works

DATEDIF counts elapsed calendar time, not rounded time. If the start date is January 15, 2020 and the end date is January 14, 2021, the "Y" unit returns 0, because a full year has not passed yet. On January 15, 2021 it returns 1.

The "M" unit works the same way at month level. From January 15 to February 14 is 0 complete months. From January 15 to February 15 is 1.

The "D" unit is the simplest. It counts the total number of days between the two dates, the same way subtracting one date from the other does.

The three combined units are where DATEDIF earns its place. "YM" gives you the leftover months after whole years are removed, so a gap of 2 years and 5 months returns 5. "MD" gives the leftover days after whole months are removed. "YD" gives the leftover days after whole years are removed.

Those combined units are what let you build a clean age string such as "34 years, 5 months, 12 days" from a single birth date. You concatenate three DATEDIF calls with the TEXT function to format the numbers.

One behavior worth knowing: if start_date is later than end_date, DATEDIF returns a #NUM! error instead of a negative number. The related VBA DateDiff function behaves differently and does return a negative value when the first date is later [1]. Do not assume the worksheet function and the VBA function match on this point.

Worked Example

Suppose you keep an employee roster with a hire date in column A and a review date in column B. You want the length of service at the review date.

For a hire date of January 10, 2020 and a review date of March 22, 2023, the three component formulas are:

=DATEDIF(A2, B2, "Y")    returns 3
=DATEDIF(A2, B2, "YM")   returns 2
=DATEDIF(A2, B2, "MD")   returns 12

Three complete years pass on January 10, 2023. From that date to March 22, 2023 is 2 complete months plus 12 days. Combining them gives a service length of 3 years, 2 months and 12 days.

To display that as one label, join the pieces with the ampersand operator.

=DATEDIF(A2,B2,"Y") & " years, " & DATEDIF(A2,B2,"YM") & " months, " & DATEDIF(A2,B2,"MD") & " days"

The same pattern works for any elapsed-time label. Swap the hire date for a project start date and you have a duration column.

More Examples

Age from a birth date. With the birth date in A2 and today's date from TODAY(), the formula =DATEDIF(A2, TODAY(), "Y") returns the person's current age in whole years. This is the most common use of DATEDIF in Excel, and it stays correct because TODAY() recalculates each time the workbook opens.

Exact age in years and months. =DATEDIF(A2, TODAY(), "Y") & "y " & DATEDIF(A2, TODAY(), "YM") & "m" produces a compact label such as "41y 7m". This is useful for pediatric or veterinary records where months matter.

Days until a deadline. =DATEDIF(TODAY(), C2, "D") returns the number of days remaining until the date in C2. If C2 has already passed, the formula returns #NUM! instead of a negative count, so wrap it in IFERROR if you want a clean blank or a zero.

Whole months of a subscription. =DATEDIF(A2, B2, "M") gives the number of complete billing months between a signup date and a cancellation date. This pairs well with conditional logic such as the COUNTIF function in Excel when you want to count how many accounts fall into each tenure band.

Days ignoring years. =DATEDIF(A2, B2, "YD") returns the day offset within the year. It is handy for anniversary calculations where you care about the calendar position, not the year count.

Combining with text formatting. Because DATEDIF returns a plain number, you can feed it into the Excel TEXT function to control decimals or padding, or into SUMIF and SUMIFS style aggregations if you first materialize the result in a helper column.

Errors and How to Fix Them

Error	Cause	Fix
`#NUM!`	start_date is later than end_date, or the unit is not one of the six valid codes	Swap the arguments, fix the unit code, or wrap the formula in IFERROR
`#VALUE!`	A date argument is text that Excel cannot read as a date	Convert the text to a real date with DATEVALUE or fix the source format
`#NAME?`	The unit code is missing its quotes, or the function name is misspelled	Put the unit code in double quotes and check the spelling of DATEDIF
Wrong-looking result	The cell is formatted as a date instead of a number	Set the cell format to General or Number

The #NUM! case is the one people hit most often. It usually means the columns are reversed, or that a future date was compared against today when the intent was the other way around.

Common Mistakes

Typing the unit without quotes. =DATEDIF(A2,B2,Y) fails. The unit must be a text string, so write "Y".
Expecting DATEDIF to appear in autocomplete. It will not. Type the full function name and the opening parenthesis yourself.
Assuming "M" means total months. It returns complete months only. If you want total months including partial ones, you need a different calculation.
Using "MD" for anything financial. The "MD" unit is known to produce incorrect results in some edge cases involving month lengths. Use it for display labels, not for calculations that feed other formulas.
Reversing the arguments. DATEDIF does not return a negative number. It errors. Check which date is earlier before you write the formula.
Forgetting that dates are numbers. If a date cell is actually text, DATEDIF fails. Confirm the cell is right-aligned by default, which is how Excel displays real dates.

Limitations

DATEDIF is kept for compatibility with Lotus 1-2-3, and although Microsoft publishes a support page for it, it is not treated like the regular listed functions. It has worked consistently across versions for many years, but Microsoft does not list it in the Insert Function dialog, and it will not appear in autocomplete as you type.

The "MD" unit is the weakest part of the function. It is documented by Microsoft as returning incorrect results in certain cases, and the errors tend to appear when the start date has more days than the end date's month. Treat "MD" as a display convenience only.

DATEDIF also cannot return fractional results. If you need 2.5 years, you have to divide a day count by 365 or use YEARFRAC instead. And it has no concept of business days, so it will happily count weekends and holidays as elapsed time. For working-day counts you need NETWORKDAYS.

Frequently Asked Questions

Why is DATEDIF not showing up in Excel?

DATEDIF is a hidden function. Excel keeps it for backward compatibility with Lotus 1-2-3 but does not list it in the function wizard or autocomplete. Type the name in full and it will calculate normally.

What is the difference between "M" and "YM" in DATEDIF?

"M" returns the total number of complete months between the two dates. "YM" returns only the leftover months after whole years are removed. For a gap of 2 years and 5 months, "M" returns 29 and "YM" returns 5.

How do I calculate age in years and months with DATEDIF?

Use two calls joined with text. =DATEDIF(A2,TODAY(),"Y") & " years " & DATEDIF(A2,TODAY(),"YM") & " months" gives a readable age label that updates automatically each day.

Why does DATEDIF give a #NUM! error?

The start date is later than the end date. DATEDIF does not return negative values, so it errors instead. Swap the two date arguments, or wrap the formula in IFERROR to show a blank or a custom message.

Is DATEDIF the same as the VBA DateDiff function?

No. They share a name and a purpose but differ in behavior. The VBA DateDiff function returns a negative number when the first date is later than the second, and it takes extra arguments for the first day of the week and the first week of the year [1]. The worksheet DATEDIF function takes only three arguments and errors on reversed dates.

References

DateDiff function (Visual Basic for Applications) | Microsoft Learn