DATEDIF in Excel: Calculate the Time Between Dates

Formulas & functions · Updated November 20, 2026

In Excel, DATEDIF calculates the time between two dates in completed years, months, or days. Use the syntax =DATEDIF(start_date,end_date,unit); for example, it can return a person’s completed age in years or the number of days between two deadlines.

What DATEDIF does—and why it is unusual

DATEDIF is a date-and-time function that compares a starting date with an ending date. Unlike subtracting one date from another, which returns the number of elapsed days, DATEDIF can report whole calendar units. Its most familiar use is calculating completed years for an age, but it can also find full months, remaining months after full years, and other date intervals.

The function is unusual because Excel retains it mainly for compatibility with older spreadsheet programs. It works in current Excel for Microsoft 365 on Windows, but it may not appear in formula autocomplete or the function list. You can still type it directly into a cell. Its relative obscurity does not mean that it is a custom add-in or a VBA function.

Excel stores dates as serial numbers, so DATEDIF needs actual date values (or formulas that return dates), not text that merely looks like a date. A cell formatted to display a date is still a number underneath. Conversely, typing a date string that Excel does not recognize can cause errors or an unintended interpretation based on regional date settings.

Syntax and the six unit codes

The syntax has three arguments: the start date, the end date, and a unit code in quotation marks. The start date must be earlier than or equal to the end date. The unit code tells Excel which interval to return:

  • "Y" returns the number of complete years.
  • "M" returns the number of complete months in the whole interval, not just the month component after counting years.
  • "D" returns the number of elapsed days.
  • "YM" returns the month remainder after complete years are removed.
  • "YD" returns the day difference after years are ignored.
  • "MD" returns the day remainder after months and years are ignored; Microsoft cautions that this unit can produce incorrect results in some situations.

Put the unit code in straight double quotation marks. The codes are not interchangeable: for instance, "M" might return 58, while "YM" returns only the leftover months within the current year-and-month breakdown.

Set up dates and calculate a basic result

For a simple worksheet, put a start date in A2 and an end date in B2. Enter real dates in those cells, then use the relevant formula in another cell. The examples below assume Excel’s standard comma argument separators; some regional settings display semicolons instead. In that case, use the separator Excel expects when entering a formula.

With the start date in A2 as January 15, 2020, and the end date in B2 as November 20, 2024, this formula returns 4, because four full anniversaries have passed:

With the start date in A2 and the end date in B2, use:

=DATEDIF(A2,B2,"Y")

For the same dates, this returns 58 complete months. This is the full interval in months, not the 10-month remainder after four years:

With the same dates in A2 and B2, use:

=DATEDIF(A2,B2,"M")

To return the total elapsed days, subtracting the dates gives the same kind of result as DATEDIF’s "D" unit. For January 15, 2020, through November 20, 2024, the difference is 1,771 days:

With the start date in A2 and the end date in B2, use:

=DATEDIF(A2,B2,"D")

These day counts measure the difference between date values; they do not count both the start and end dates as separate full days. If a business rule requires counting every calendar date touched, including both endpoints, add one to the elapsed-day result and make that convention clear.

Build a years, months, and days breakdown

A common request is a duration such as “4 years, 10 months, 5 days.” For the example dates above, the complete years are 4, the remaining months are 10, and the remaining days are 5. The "YM" and "MD" units are intended to provide the remainder components for this kind of breakdown, but the limitations of "MD" matter. For uncomplicated dates such as this example, a combined display can be useful:

With the start date in A2 and the end date in B2, use:

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

The formula joins numbers and text into one text result. That is convenient for a report, but separate numeric results are usually better if you intend to sort, sum, or reuse the components in later calculations. Also, do not treat this display as a universal measure of duration: a month is not a fixed number of days, and edge dates can make remainder calculations less intuitive.

Choose the unit that matches the question

Before writing a formula, decide what “time between” means for your task. These measures answer different questions, so choosing the wrong unit can make a valid formula misleading:

  • For someone’s age in whole years, use "Y". It counts completed anniversaries, not the year numbers shown on the calendar.
  • For a total number of completed months, use "M". It does not return a decimal such as 58.2 months.
  • For elapsed calendar days, use "D" or subtract the earlier date from the later one.
  • For a years-and-months display, use "Y" with "YM", and decide whether a day remainder is needed.
  • For workdays, DATEDIF is not the right function: it counts calendar time and does not exclude weekends or holidays.

DATEDIF is not a way to convert months into a fixed number of days. Calendar months vary in length, and a year can include a leap day. If a report needs a decimal number of years or a defined day-count convention, use a method that matches that definition rather than assuming that every year is 365 days.

Common errors and date edge cases

If the start date is later than the end date, DATEDIF returns #NUM!. Check the order of the two date cells; if the dates can occur in either order, first decide whether the result should be an absolute duration or whether reversed dates should be flagged as data errors. Silently rearranging dates may hide a genuine mistake.

A #VALUE! error often indicates that one of the arguments is not a valid date value. Dates imported from another system may be text, even if they look like dates on screen. Check the cell contents and convert text dates to real Excel dates before calculating. If a formula is typed with an invalid unit code, correct it to one of the six supported codes.

Month-end and leap-day dates deserve special care. A person born on February 29 has no February 29 birthday in a non-leap year; DATEDIF does not choose a legal or organizational rule for when that person’s age should advance. Likewise, counting completed months between dates near the end of a month may not match an informal idea of “one month later.” Set the policy required for the task and verify results for boundary dates.

Be especially cautious with "MD". It can behave unexpectedly when the ending day number is earlier than the starting day number, and Microsoft warns of known limitations for this unit. Do not rely on it for critical billing, eligibility, or compliance calculations without testing representative dates and confirming the intended rule. A formula that works for dates on the 15th and 20th does not prove that it handles dates such as January 31 through February 28 correctly.

DATEDIF compared with other date calculations

For elapsed days, ordinary subtraction is often simpler and easier to inspect. With dates in A2 and B2, the formula =B2-A2 returns the number of days between them, provided B2 is the later date. Format the result as a number if Excel displays it as a date. DATEDIF with "D" is more explicit about the intended calculation, but it does not add different day-count behavior.

For workdays, Excel’s NETWORKDAYS function can count weekdays while excluding an optional holiday list; DATEDIF cannot. For decimal-year calculations, YEARFRAC offers a different result and includes a basis argument that affects the day-count convention. Neither function is automatically the right choice for every policy: select one based on the definition required by the report, contract, or analysis.

For age in completed years, DATEDIF is compact, but the answer still depends on valid birth and as-of dates and on the policy for leap-day birthdays. A formula’s output should not be mistaken for a legal, medical, or eligibility determination where a formal rule controls.

Frequently Asked Questions

Why can’t I find DATEDIF in Excel’s function list?
DATEDIF is retained for compatibility and may not appear in autocomplete or the usual function list. Type the function and its arguments directly into the formula bar or a cell.

Does DATEDIF count the start date?
No. It calculates the interval between date values; it does not count both endpoints as separate elapsed days. If a process defines an inclusive date count, adjust the calculation to follow that rule.

How do I calculate age in Excel with DATEDIF?
Put a birth date in A2 and the date to calculate age on in B2. With those dates in A2 and B2, use =DATEDIF(A2,B2,"Y") to return completed years. For age as of today, use =DATEDIF(A2,TODAY(),"Y") when A2 contains the birth date; the result updates as the current date changes.

What is the difference between “M” and “YM”?
"M" returns the total number of completed months across the entire interval. "YM" ignores complete years and returns only the leftover months in the year-and-month breakdown.

Does DATEDIF work in Excel for Mac and Excel for the web?
DATEDIF is available in current Excel versions, including Microsoft 365 on Mac and Excel for the web. The formula’s purpose is the same; keyboard shortcuts and some interface details differ by platform, but no special menu command is required to use it.

Practical checks before relying on the result

For a dependable workbook, label the start and end date columns, store dates as date values, and state whether the result is elapsed days, completed months, or completed years. Test the formula against a few known cases: dates one day apart, dates around a month-end, a leap-day date, and a reversed pair if that can occur in your data. Keep the components in separate cells when calculations depend on them, and document any business rule for inclusive counts or anniversaries. DATEDIF is useful when its completed-unit logic matches the question; it cannot decide what a month, age threshold, or inclusive period should mean for your particular process.

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.