DATEDIF in Excel: Is It Still Available?

Formulas & functions · Updated November 22, 2026

Yes—DATEDIF still works in Microsoft Excel for Microsoft 365, even though it is a legacy function that may not appear in autocomplete or the Insert Function list. Enter it directly in a cell to calculate the number of complete years, months, or days between two dates; the key is choosing the right unit and supplying valid dates in the correct order.

Why DATEDIF seems to have disappeared

DATEDIF is unusual because Excel supports it for compatibility with older spreadsheet files, but does not present it like most current functions. As a result, typing “DATEDIF” may not trigger the usual function suggestions, and searching the Insert Function dialog may not find it. That absence does not mean the function has been removed: a correctly entered DATEDIF formula can still calculate in current Excel.

The function has a long history in spreadsheets and is commonly described as a compatibility function. Microsoft’s documentation notes that it is provided for compatibility with older workbooks. That legacy status helps explain the limited help and suggestions, but it does not make the function obsolete for ordinary date calculations.

In Excel for Microsoft 365 on Windows, type the function directly into a cell and press Enter. Excel for Mac uses the same function and formula syntax. Excel for the web also supports many standard worksheet formulas, though its suggestions and interface can differ. If a workbook will be opened in a different spreadsheet app, check that app’s support before relying on DATEDIF.

Syntax and the six unit codes

DATEDIF takes three arguments in this order: a start date, an end date, and a unit code in quotation marks. The start date must be earlier than or the same as the end date. Use actual Excel dates in the first two arguments, rather than date-looking text where possible. The unit determines whether the result is a count of complete calendar periods or a total number of days.

  • Y returns complete years between the dates.
  • M returns complete months between the dates.
  • D returns the total number of elapsed days.
  • YM returns the months remaining after complete years are excluded.
  • YD returns the days remaining after complete years are excluded.
  • MD returns the days remaining after complete months and years are excluded. Microsoft warns that this unit can produce an inaccurate or negative result in some situations.

These codes are not interchangeable. For example, M counts complete months across the entire interval; it does not return the month number on a calendar. YM is a remainder-style result that ignores complete years. The unit codes are text, so include quotation marks around them in the formula. A misspelled or unsupported code can cause an error.

Examples: age and elapsed days

For a simple age calculation, put a date of birth in A2 and the date to calculate age on in B2. With A2 containing June 15, 1985, and B2 containing November 3, 2026, this formula returns 41, because 41 complete years have passed:

With the date of birth in A2 and the as-of date in B2, calculate completed years using =DATEDIF(A2,B2,"Y"). For June 15, 1985, through November 3, 2026, the result is 41.

To show a more human-readable age, combine the three components. In the same cells, the following returns “41 years, 4 months, 19 days.” The day remainder is calculated after four complete months have elapsed from the June 15 anniversary date.

With A2 containing June 15, 1985, and B2 containing November 3, 2026, use =DATEDIF(A2,B2,"Y")&" years, "&DATEDIF(A2,B2,"YM")&" months, "&DATEDIF(A2,B2,"MD")&" days". The displayed result is 41 years, 4 months, 19 days.

This is a familiar age-display pattern, but the MD warning matters: DATEDIF’s month-and-day remainder can behave unexpectedly around some month-end dates. For a report that must be correct for every possible birthday, test dates such as the last day of a month and leap-day birthdays, and consider a custom calculation or a clearly defined age policy instead of assuming MD is universally reliable.

For total elapsed days, use D as the unit. With A2 containing January 1, 2025, and B2 containing May 31, 2025, the result is 150 days. This is elapsed time: January 1 is the starting point, not an extra day added to the count.

With January 1, 2025, in A2 and May 31, 2025, in B2, calculate total elapsed days using =DATEDIF(A2,B2,"D"). The result is 150.

What “complete” years and months mean

DATEDIF’s Y and M units count completed periods, not rounded or approximate periods. If a person is one day short of a birthday, Y does not round up to the next year. Likewise, M does not count a month merely because the dates fall in different named months: it counts complete months according to the dates’ day positions as well.

That distinction matters for contracts, service anniversaries, subscriptions, and eligibility rules. “How many calendar months are touched?” is a different question from “How many complete months elapsed?” DATEDIF with M answers the second question. If a business rule counts any partial month as a full month, DATEDIF is not the right rule without additional logic.

D is different: it returns the total elapsed days, including all days across intervening months and years. It does not split the interval into years and leftover days. Also, a date interval is not the same thing as a count of inclusive calendar dates. If a project runs from Monday through Friday and the desired answer is five dates including both endpoints, elapsed-day subtraction alone gives four; define whether the start and end dates should both count before choosing a formula.

Common errors and date pitfalls

  • The start date is later than the end date. DATEDIF returns a #NUM! error in this case. Swap the dates if the interval should always be positive, or handle reversed dates explicitly.
  • A date is stored as text or interpreted differently by locale. A string such as 03/04/2025 can mean March 4 or April 3 depending on regional settings. Build unambiguous dates with Excel’s DATE function or enter dates using a format your system recognizes, then verify the cell value.
  • The unit is not quoted or is misspelled. Put a unit such as Y or YM in quotation marks. Check for extra spaces or a code that is not one of the supported options.
  • A blank or invalid date is included. A blank cell may be treated like a zero date in calculations, while text that only looks like a date may not behave as expected. Validate the input cells and decide how blanks should be handled before filling a formula down a column.
  • MD gives a surprising answer. Microsoft specifically cautions about this unit. Results can be unreliable around month ends, so do not use it as an unquestioned universal way to calculate leftover days.

Excel stores dates as serial values, and workbooks can use different date systems. For a normal workbook created and used in Excel, entering real dates is usually enough; problems are more likely when dates have been imported, pasted as text, or moved between systems. When results look wrong, check the underlying cell values and number formats rather than changing the formula at random.

When to use DATEDIF or an alternative

Choose DATEDIF when the question is naturally expressed as completed years or months between two dates, such as a basic age, tenure, or elapsed-period calculation. Its short unit codes make the intention compact, and the function is available in current Excel. For total days, D is straightforward, although subtracting one date cell from another can be simpler when a plain day difference is all that is needed.

For a fractional year—for example, an approximate year-based interval used in a model—YEARFRAC is a different tool. Its result is a fraction, not a count of completed birthdays or months, and its basis argument changes the day-count convention. Do not substitute it for DATEDIF Y unless fractional years are actually what the calculation requires.

With A2 containing January 1, 2025, and B2 containing May 31, 2025, YEARFRAC using its actual/actual basis returns approximately 0.41096 years (150 divided by 365). Use =YEARFRAC(A2,B2,1) for that fractional-year result.

There is also a practical difference between a calculation and a display. A formula that returns 41 is useful for sorting or comparing ages; a formula that joins years, months, and days into text is easier to read but is text, not a numeric duration. Keep the numeric components separate if later formulas need to compare or total them. You can create a readable label in a separate column.

Frequently Asked Questions

Did Microsoft remove DATEDIF from Excel?
No. It remains usable in current Excel, including Microsoft 365, but it is a legacy compatibility function and may not appear in autocomplete or the function browser. Enter the formula directly in a worksheet cell.

Why is DATEDIF missing from Excel’s function suggestions?
Excel does not expose DATEDIF in the same way as many regularly documented functions. Missing suggestions do not prove that the function is unavailable. Type the function name and its arguments directly, then check the result.

Can DATEDIF calculate someone’s age?
Yes. Put the birth date first and the as-of date second, and use the Y unit for completed years. For accurate reporting, confirm how your organization treats birthdays on February 29 and test the dates relevant to your use case.

What is the difference between Y and M?
Y counts completed years across the interval; M counts completed months across the whole interval. YM does something else: it gives the remaining months after complete years are excluded. Pick the unit that matches the question rather than assuming all three are calendar labels.

Does DATEDIF work in Excel for Mac or Excel for the web?
The function syntax is the same in Excel for Mac, and current Excel for the web supports the function in worksheet formulas. The interface and formula suggestions may differ from Windows desktop Excel, so enter the function directly if it is not suggested.

Is DATEDIF safe to use in a shared workbook?
It is a reasonable choice when collaborators use Excel, but its hidden status can confuse people maintaining the file. Add a brief note explaining the unit codes, validate date inputs, and test any workbook opened in another spreadsheet program. Avoid relying on MD without checking its edge cases.

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.