DATEDIF in Excel: Calculate Years, Months, and Days

Formulas & functions · Updated November 20, 2026

In Excel for Microsoft 365, DATEDIF calculates the number of complete years, months, or days between two dates. If the earlier date is in A2 and the later date is in B2, for example, enter =DATEDIF(A2,B2,"Y") to return the number of complete years between them.

What DATEDIF calculates

DATEDIF measures the elapsed time between a start date and an end date, returning an integer count in the unit you request. It does not calculate a decimal fraction of a year or month. That makes it useful for questions such as how many full years someone has lived, how many complete months a project has lasted, or how many days separate two dates.

The function has an unusual history: Excel retains it for compatibility with older spreadsheet software, and it is less visible in Excel’s function tools than many newer functions. You can still type it directly into a cell. Excel does not always offer DATEDIF in formula autocomplete or the Insert Function dialog, so its absence from a suggestion list does not mean the function cannot be used.

The order of the dates matters. DATEDIF expects the start date to be earlier than or equal to the end date. If the start date is later, the result is an error rather than a negative duration. If you need to measure time in reverse, swap the dates or choose a calculation designed to handle signed differences.

Syntax and the six unit codes

The function takes three arguments: the start date, the end date, and a text unit code. The unit controls what DATEDIF counts. Use uppercase or lowercase letters; the codes are not case-sensitive.

  • Y returns the number of complete years.
  • M returns the number of complete months, not just the month-number difference.
  • D returns the total number of days between the dates.
  • YM returns the remaining months after complete years are excluded.
  • YD returns the number of days after complete years are excluded.
  • MD returns the number of days after complete years and months are excluded. Microsoft warns that this unit can produce negative, zero, or inaccurate results in some situations, so treat it cautiously.

These codes answer different questions. For instance, M is appropriate when you need a total count of completed months, while YM is for the month component left over after counting complete years. A result of 70 months can therefore be correct even though it represents 5 years and 10 months.

Make sure Excel recognizes your dates

DATEDIF works best when both inputs are genuine Excel dates, rather than text that merely looks like a date. Excel stores dates as serial numbers and displays them according to the cell’s number format. If a date was imported from a website or another system, it may be text, or Excel may have interpreted its day and month in the wrong order because of regional settings.

To check a result, select a date cell and look at the formula bar and number format. A date can be displayed as 18-Oct-1990 or 10/18/1990 and still be the same underlying value. If Excel treats a date as text, change the source to a real date value before relying on the interval. Re-entering the date in a format Excel recognizes, or converting imported text with an appropriate date-conversion process, can resolve the problem.

When entering dates directly, use the date format your copy of Excel recognizes. A value such as 03/04/2024 can mean March 4 or April 3 depending on regional conventions, so a written month or an unambiguous format can prevent mistakes. Also confirm that the end date is not earlier than the start date; a reversed pair commonly causes a #NUM! error.

Examples for elapsed time and age

Suppose A2 contains the real date 15-Mar-2018 and B2 contains 14-Feb-2024. With those dates in A2 and B2, use =DATEDIF(A2,B2,"Y") to get 5 complete years. The sixth anniversary, 15-Mar-2024, has not yet occurred by the end date. With the same cell layout, =DATEDIF(A2,B2,"M") returns 70 complete months: the 70-month point was 15-Jan-2024, while the 71-month point had not arrived by 14-Feb-2024.

For an age expressed as years and leftover months, suppose A2 contains a birth date of 18-Oct-1990 and B2 contains an as-of date of 15-Jun-2024. With that layout, =DATEDIF(A2,B2,"Y") returns 33. With the same dates, =DATEDIF(A2,B2,"YM") returns 7, because seven complete months have passed since the 18-Oct-2023 anniversary. Those two values can be displayed as 33 years and 7 months.

If you also want the remaining day component for that example, A2 is still 18-Oct-1990 and B2 is still 15-Jun-2024. The formula =DATEDIF(A2,B2,"MD") returns 28 for these dates. However, MD is the least dependable unit: Microsoft cautions that it can give unexpected results for some date combinations. For important reporting, validate the result against the dates and the rules your organization uses, rather than assuming MD always produces a reliable calendar remainder.

For a simple total-days calculation, keep A2 as 15-Mar-2018 and B2 as 14-Feb-2024. The formula =DATEDIF(A2,B2,"D") returns 2,162 days. This counts elapsed days, not both endpoints as separate inclusive calendar dates. If a process requires inclusive counting—for example, counting every date on a schedule—its business rule may require adding one to the elapsed-day result.

Common errors and how to fix them

  • #NUM! usually means the start date is after the end date. Check the inputs and their order, including whether one of the dates was interpreted incorrectly.
  • #VALUE! can indicate that an argument is not a valid date value, or that a unit code has been entered incorrectly. Check for text dates and use one of the six supported codes.
  • A result that is one year or month lower than expected often means the next anniversary has not yet been reached. DATEDIF counts completed intervals, not calendar labels. A person born in October is not a year older in January simply because the calendar year changed.
  • A result of zero is not necessarily an error. Two dates can be several days apart but still have zero complete months or zero complete years between them.
  • A strange MD answer may reflect the unit’s known limitations rather than a typing mistake. Test the dates and consider a different method if exact month-and-day decomposition matters.

Also check that the formula’s unit is in quotation marks and that the cell references point to the intended dates. In a localized Excel installation, the list separator used between arguments may depend on regional settings. The examples here use commas, which are standard in many English-language installations.

DATEDIF compared with other date calculations

Subtracting one date from another is often the simplest alternative when the desired answer is total elapsed days. Since Excel stores valid dates as serial values, subtracting the earlier date from the later one returns a day count. DATEDIF with D is more explicit about the intent, while direct subtraction is easy to inspect and can be convenient in larger calculations.

For a decimal year estimate, YEARFRAC is a different function: it returns a fraction of a year according to a selected day-count basis. It is not interchangeable with DATEDIF Y, which counts only completed years. When a result is used in a policy, contract, or payment calculation, choose the method that matches the applicable definition of a year or month; spreadsheet functions do not decide that rule for you.

DATEDIF is useful when the requirement is specifically a count of completed intervals. It is less suitable when you need a signed difference, an inclusive range, a decimal duration, or a rigorously defined calendar breakdown involving the last day of a month. In those cases, choose a method that states the intended convention and verify it with boundary dates such as month-end and leap-day examples.

Practical tips for reliable results

Keep the source dates in separate cells and use cell references instead of embedding date text in formulas. This makes it easier to audit the inputs, update an as-of date, and fill the calculation down a table. Format the result as a number or as explanatory text, depending on whether the value will be used in later calculations.

For a list of people or projects, test the formula on records near anniversaries and month-end before copying it throughout the worksheet. Pay particular attention to February 29 birthdays, dates such as the 30th or 31st, and records where the end date falls just before a monthly anniversary. These boundary cases expose assumptions that a typical mid-month example may not reveal.

If you calculate age for a report, make the as-of date explicit when reproducibility matters. A worksheet based on a fixed reporting date will return the same result when reopened later, while a calculation tied to the current date changes over time. Whichever approach you use, document what the date represents so another reader does not mistake an age at a past reporting date for a current age.

Frequently Asked Questions

Why can’t I find DATEDIF in Excel’s function list?
DATEDIF is retained for compatibility and may not appear in the Insert Function list or formula autocomplete. In Microsoft 365 on Windows, type the function directly into a cell, using valid date references and a supported unit code.

Does DATEDIF count the start date or end date?
It measures the elapsed interval between the two dates. For D, it returns the difference in days, so a range from one date to the next day is one day, not two. If your task counts both endpoints, apply that rule separately.

What is the difference between M and YM?
M counts all complete months in the interval. YM gives only the leftover month count after full years are removed. For example, a result of 70 with M corresponds to 5 complete years and 10 remaining months, so YM would return 10 for the same date pair.

Can DATEDIF return a negative number?
No. The start date must be earlier than or equal to the end date; a reversed date order generally returns #NUM!. Swap the references if you simply need the elapsed interval in the other direction.

Is DATEDIF accurate for calculating age?
The Y unit returns completed years between valid dates and is commonly used for age at a specified date. Make sure the as-of date is correct, and check special cases such as February 29 against the age convention you need. Avoid relying on MD without validation because it has known limitations.

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.