DATEDIF in Excel: Formula, Examples, and Common Errors

Formulas & functions · Updated November 22, 2026

The DATEDIF formula in Excel calculates the time between two dates in days, months, or years: =DATEDIF(start_date,end_date,unit). It is useful for finding someone’s age or the number of complete years or months between dates, but the start date must be earlier than or equal to the end date.

What DATEDIF does

DATEDIF returns the difference between two dates according to the unit you choose. Unlike simply subtracting one date from another, it can count completed calendar years or months, as well as days. That makes it useful when a question is about elapsed time in calendar units: for example, how many full years someone has lived or how many whole months a service period has lasted.

The function is supported in Microsoft Excel for Microsoft 365 on Windows, but it is a legacy compatibility function retained from other spreadsheet software. Excel may not suggest DATEDIF as you type or show it in the function list, even though you can enter it directly in a cell. Its limited visibility is not evidence that the function is unavailable. Microsoft also cautions that some results, especially with the MD unit, can be inaccurate.

Syntax and available units

The syntax has three arguments: a starting date, an ending date, and a quoted text code that tells Excel what to calculate. Use a date in a cell or a formula that returns a date for each of the first two arguments. The unit is not a cell format; it is a text value supplied to the function.

  • "Y" returns the number of complete years between the dates.
  • "M" returns the number of complete months between the dates.
  • "D" returns the total number of days between the dates.
  • "YM" returns the remaining months after complete years are counted.
  • "YD" returns the days between the dates as if they were in the same year, ignoring the years.
  • "MD" returns the remaining days after months and years are ignored; Microsoft warns that this unit can produce incorrect results.

Use the quotation marks around the unit. The codes are commonly written in uppercase for readability. “Complete” matters: DATEDIF does not simply count the number of calendar boundaries crossed. It measures whether a full year or month has elapsed from the start date.

Worked example with two dates

Suppose A2 contains the actual Excel date June 15, 1990, and B2 contains November 3, 2026. The formulas below use those two cells. This example shows the distinction between a total and a remainder.

With the start date in A2 and the end date in B2, use this formula to count full years; it returns 36:

=DATEDIF(A2,B2,"Y")

With the same dates in A2 and B2, this formula returns full months, or 436:

=DATEDIF(A2,B2,"M")

With the same dates in A2 and B2, this formula returns total elapsed days, or 13,290:

=DATEDIF(A2,B2,"D")

The day total is the actual date difference; it is not 36 multiplied by 365, because leap days are included. With the same cell layout, the YM unit returns 4 remaining months after full years are counted:

=DATEDIF(A2,B2,"YM")

So this date range is 36 complete years and 4 additional complete months. DATEDIF does not return a combined years-months-days phrase automatically; you can calculate the components separately or join values into text if that format is useful.

Calculate age in years or years and months

A common use is calculating age from a birth date. If A2 contains a birth date and you want age as of today, enter the following formula in another cell. It returns the number of complete years:

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

TODAY() supplies the current date and updates when Excel recalculates the workbook. If the age needs to be calculated as of a particular date instead, put that date in B2 and use the formula below. With a birth date in A2 and the chosen as-of date in B2, it returns complete years:

=DATEDIF(A2,B2,"Y")

To show years and remaining complete months as one text value, use the formula below with the birth date in A2 and the as-of date in B2. It returns a result such as “36 years, 4 months” for the worked example:

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

The ampersand joins the numeric results with text. Because the output is text, it is convenient for a label but less suitable for later calculations. Keep the numeric results in separate cells if you need to sort, sum, or compare ages.

How DATEDIF treats months and days

DATEDIF’s complete-month calculation can surprise people near the end of a month. For instance, January 31 to February 28 is 28 elapsed days, but it is not a full month according to the function’s day-of-month comparison. This is one reason a result based on calendar-month names can differ from a result based on complete elapsed months. Decide what “one month” means for your task before choosing a formula.

The D unit is the straightforward choice for a total day count. YM, YD, and MD are component-style results: they ignore larger units rather than reporting a total. For example, YM excludes complete years, so it can return a small remainder even when the overall interval spans decades. Do not add component results together as though they were uniform units, or assume that a “days” remainder has the same meaning as total elapsed days.

Use MD with particular care. Microsoft documents that this unit can return a negative number, zero, or an inaccurate result in some situations. If a report must reliably express an interval as years, months, and days for many kinds of dates, check the results against the business rules you need; DATEDIF’s component behavior is not a universal definition of calendar duration.

Enter dates Excel can recognize

DATEDIF works with Excel date values, which are stored as numbers and displayed using a date format. A cell that merely looks like a date may instead contain text imported from another system. If Excel does not recognize the value as a date, the formula can return an error or behave differently from what you expect.

To check the input, select a date cell and look at its value in the formula bar, or temporarily apply a number format such as General. A recognized date displays as a serial number in General format. If the value remains text, convert it to a real date using a suitable import or conversion method before relying on the calculation. Be mindful of regional date conventions: a text value such as 4/5/2025 can mean April 5 or May 4 depending on how it was interpreted when entered or imported.

Also check the order of the dates. The start date must not come after the end date. For a calculation “as of today,” a future date in A2 can therefore produce an error rather than a negative age. Validate the source data or decide explicitly how future dates should be handled instead of silently treating an error as a valid result.

Common errors and ways to troubleshoot

  • #NUM! commonly indicates that the start date is later than the end date or that the unit argument is not one of the recognized codes. Check the order and spelling of the arguments.
  • #VALUE! can indicate that Excel cannot interpret an argument as a valid date. Check for text dates, blanks, or values imported in an unexpected format.
  • A result of zero or a smaller number than expected may be correct if a full year or month has not elapsed. Compare the actual day of the month in each date, not just their month names.
  • The formula does not appear in autocomplete does not by itself mean it is unsupported. Type the function and its arguments directly, including the opening parenthesis.
  • A result changes after opening a workbook elsewhere may relate to date interpretation, calculation settings, or a different spreadsheet program’s handling of dates. Confirm both the underlying dates and the intended calculation.

In Excel for Microsoft 365 on Windows, formulas use commas between arguments under the default regional settings. Some installations use semicolons because of regional settings; if Excel rejects a correctly written formula, check the list separator used by that installation. Excel for Mac and Excel for the web also support DATEDIF, though interface details and regional separators can differ. The formula’s core function and unit codes are the same.

When to use another approach

DATEDIF is a practical choice when you need completed years or months, or the total number of days between two real dates. For a fixed day count, ordinary date subtraction is another simple option: with the starting date in A2 and the ending date in B2, =B2-A2 returns the elapsed number of days when both cells hold Excel dates. Format the result as a number if it displays as a date.

For a number of months based on a different rule—such as counting month boundaries, treating end-of-month dates specially, or prorating partial months—DATEDIF may not match the requirement. Write down the rule and test edge cases such as January 31, leap day, and a date just before an anniversary. Financial or contractual definitions of a month may come from the relevant agreement or policy, not from Excel’s default date arithmetic.

If a duration will be used in later calculations, retain separate numeric results rather than converting everything to a formatted text label. That makes it easier to audit the calculation and apply a consistent rounding or reporting rule elsewhere in the workbook.

Frequently Asked Questions

Why can’t I find DATEDIF in Excel’s function list?
DATEDIF is a legacy compatibility function and may not appear in autocomplete or the function wizard. You can still type it directly into a worksheet cell in supported Excel versions.

Does DATEDIF count the start date?
No. The D unit reports the elapsed difference between the two date values, so the starting date is not counted as an extra day. For example, two consecutive dates differ by one day.

What is the difference between DATEDIF with M and YM?
M returns all complete months across the interval. YM returns only the remaining months after complete years are removed. A multi-year interval can therefore have a large M result and a YM result from zero through eleven.

Can DATEDIF calculate someone’s age?
Yes. Put the birth date in one cell and the date to calculate age on in another, then use the Y unit. For age as of today, TODAY() can provide the second date.

Why does DATEDIF return an error?
Check that both inputs are valid Excel dates, that the start date is not later than the end date, and that the unit is exactly Y, M, D, YM, YD, or MD. Also check whether regional settings have changed the formula argument separator.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.