Yes—DATEDIF is available in Microsoft Excel, even though it may not appear in the function list or autocomplete. Enter it directly in a cell or the formula bar; for example, with a start date in A2 and an end date in B2, =DATEDIF(A2,B2,"Y") returns the number of complete years between them.
Why DATEDIF Seems to Be Missing
DATEDIF is a supported Excel function, but it is unusual: it is retained for compatibility with older spreadsheet software and is not presented like a typical function in Excel’s menus, formula suggestions, or function documentation. That omission can make it look as if the function is unavailable. It does not mean you need to install an add-in or enable a special setting.
In Microsoft 365 for Windows, start typing the formula yourself, including the initial equals sign. Excel can calculate it even if DATEDIF does not appear in the autocomplete dropdown. Current Excel for Mac and Excel for the web also accept the function. The basic syntax is =DATEDIF(start_date,end_date,unit). The start and end arguments should be dates Excel recognizes, and the unit is a text value in quotation marks.
DATEDIF is not the same as a function that is missing because of an older Excel version. Its low visibility is the confusing part; direct entry is the practical solution.
What DATEDIF Calculates
DATEDIF measures the interval between two dates in a particular unit. It returns a whole number of complete units, rather than a decimal fraction. That distinction matters: four years and eleven months count as four complete years, not 4.9 years. Likewise, a partial month does not count as a complete month.
For a concrete example, suppose A2 contains 15-Jan-2020 and B2 contains 3-Nov-2024. With those dates in A2 and B2, =DATEDIF(A2,B2,"Y") returns 4, because four complete years have elapsed. With the same dates in A2 and B2, =DATEDIF(A2,B2,"M") returns 57 complete months. The result is 57 rather than 58 because the 15th of the month has not yet arrived in November.
Excel stores valid dates as serial numbers, so a cell may display a date while internally holding a number. DATEDIF works on the underlying date values, not on how those values look on screen. Formatting a cell to display a date does not, by itself, turn text that merely resembles a date into a real date.
DATEDIF Units and When to Use Them
The third argument tells Excel which interval to return. Enter the unit inside quotation marks. These are the standard unit codes:
"Y"returns complete years between the dates."M"returns complete months between the dates, without converting years to a separate result."D"returns the total number of days between the dates."YM"returns remaining complete months after complete years are excluded."YD"returns days after complete years are excluded."MD"attempts to return remaining days after months and years are excluded, but it has known reliability concerns and is generally best avoided.
With 15-Jan-2020 in A2 and 3-Nov-2024 in B2, the same layout gives 9 for =DATEDIF(A2,B2,"YM"): four complete years are set aside, leaving nine complete months. With those same dates, =DATEDIF(A2,B2,"D") returns 1,754, the total elapsed days. If you need days after whole years and months, be cautious about using MD; its result can be misleading around some date combinations.
Calculating Age or a Duration in Years and Months
A common use is calculating age in completed years. Put a birth date in A2. With that layout, =DATEDIF(A2,TODAY(),"Y") returns the person’s age in complete years as of the current date. TODAY updates when Excel recalculates, so the displayed age can change as time passes. For a fixed report or historical calculation, use a specific end-date cell instead of TODAY, so the result does not shift later.
To show years and leftover months in one cell, put the start date in A2 and the end date in B2. With that layout, =DATEDIF(A2,B2,"Y")&" years, "&DATEDIF(A2,B2,"YM")&" months" returns 4 years, 9 months for the example dates above. This is text for display, not a numeric duration suitable for every kind of later calculation. If you need to sort, average, or perform further arithmetic on the result, keep the numeric values in separate cells.
Age calculations can have special rules in contexts such as official records, benefits, or employment policies. A formula can calculate elapsed time, but it cannot determine which policy a particular organization or jurisdiction applies. Confirm the required convention where that distinction matters.
How to Enter the Formula and Check the Dates
In Excel for Microsoft 365 on Windows, select the result cell, type the formula beginning with =DATEDIF, and press Enter. You can also select the cell and type in the formula bar. On a Mac, the basic entry process is the same. Excel for the web also allows formulas to be entered directly in a cell. The function may not be offered in autocomplete in any of these environments, so type its name and arguments yourself.
Before relying on a result, check the inputs and output format:
- Confirm the start date is in the first argument and the end date is in the second. Reversing them can produce an error.
- Check that both cells contain real Excel dates, not text imported from another application. If a date is left-aligned or cannot be formatted as a date, it may be text, although alignment alone is not a definitive test.
- Make sure the end date is not earlier than the start date. DATEDIF generally returns a number error when the dates are in reverse order.
- Check that the unit is enclosed in straight quotation marks and is one of the supported codes, such as "Y" or "YM".
- If the displayed result looks like a calendar date instead of a number, change the result cell’s number format to General or Number.
Excel formulas in this article use commas between arguments. Depending on your computer’s regional settings, Excel may expect semicolons instead; if a correctly written formula is rejected, check the list separator used by your installation. The function name and unit codes remain the same.
Common Errors and Important Limitations
A frequent mistake is expecting DATEDIF with the "M" unit to return the number of calendar months touched by an interval. It returns complete months. For example, an interval beginning late in one month and ending early in a later month may span parts of several calendar months but still contain fewer complete months than expected. Decide whether you need complete elapsed months or a count of calendar-month boundaries before choosing a calculation.
Another common issue is an end date that precedes the start date. DATEDIF is designed to calculate forward from an earlier date to a later one, so sort or correct the dates rather than assuming the function will return a negative interval. Blank cells, text dates, and dates outside Excel’s valid range can also lead to confusing results.
Microsoft has cautioned that DATEDIF can produce incorrect results in certain scenarios. The MD unit is especially worth treating carefully; it can behave unexpectedly for some date combinations. For important reporting, test boundary cases such as month ends and leap-year dates, and compare the output with an independently checked calculation. Avoid using MD as the foundation of a consequential calculation unless you have verified the exact cases your workbook uses.
DATEDIF also does not calculate a decimal number of years or months. If your task needs a fractional year for a particular reporting convention, consider whether YEARFRAC matches that convention; it uses its own basis options and may not produce the same answer as a count of completed anniversaries. Choosing a function requires matching the definition of duration to the job, not merely selecting the one with a familiar name.
DATEDIF Compared with Simple Date Subtraction
For elapsed days, ordinary subtraction is simpler and transparent. With the start date in A2 and the end date in B2, =B2-A2 returns 1,754 for 15-Jan-2020 through 3-Nov-2024, the same total-day count as DATEDIF with the "D" unit. Format the result as General or Number if Excel displays it as a date. Subtraction can also return a negative number when the dates are reversed, which may be useful if signed intervals are intentional.
Subtraction does not by itself answer questions such as “How many birthdays have passed?” or “How many complete months have elapsed?” Those require a rule for incomplete years or months. DATEDIF provides a compact way to request those complete units, while YEAR, MONTH, and DAY can be used to build a more explicit calculation when a workbook needs a customized rule. For a more complex duration, define the rule first and test dates near month ends and leap days.
Frequently Asked Questions
Why does DATEDIF not show up in Excel autocomplete?
DATEDIF is retained for compatibility and is not surfaced like many standard functions. Type the function directly in a cell or the formula bar; the lack of a suggestion does not mean the function is unavailable.
Does DATEDIF work in Excel for Microsoft 365?
Yes. It can be entered directly in current Microsoft 365 Excel, including Windows and Mac desktop versions and Excel for the web. Its limited visibility in suggestions is separate from whether Excel can calculate it.
What is the formula for age in Excel?
With a birth date in A2, =DATEDIF(A2,TODAY(),"Y") returns completed years as of today. Use a cell containing a fixed as-of date instead of TODAY when you need a result that stays tied to a particular reporting date.
Why does DATEDIF return an error?
Check that the start date comes before the end date, both inputs are valid Excel dates, and the unit is a supported quoted code. If the formula itself is rejected, your regional settings may require semicolons rather than commas between arguments.
Can DATEDIF calculate total months or days?
Yes. Use "M" for complete months and "D" for total elapsed days. These are different measures: a year-long interval may return many total days but only the corresponding number of complete months.
Related
- Ctrl+G in Excel: Open Go To and Select Cells
- Excel Redo Shortcut: How Ctrl+Y Works and When It Fails
- Construction Project Timeline Template Excel: Build One
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Excel SUMIFS #VALUE! Error: Causes and FixesIn Excel, SUMIFS most commonly returns #VALUE! when its sum range and one or more criteria ranges have…
- Excel Column Is a Number Instead of a Letter? Fix ItIf Excel shows column headings as 1, 2, 3 instead of A, B, C, turn off R1C1 reference…
- Excel Cell Not Showing All Text? Fix ItIf an Excel cell is not showing all its text, first select it and choose Home > Wrap…
- Excel Box and Whisker Plot Not Working? Fixes to TryIf an Excel box-and-whisker plot is missing, blank, or arranged incorrectly, first check that you are using a…
- Excel Repair Now Error: Causes and Safe Fixes“Excel repair now error” is not one specific Microsoft Excel error code; it usually describes a prompt or…
- Excel IRR #DIV/0! Error: Why It Happens and How to FixIf Excel shows #DIV/0! beside IRR, the error is usually in one of the cash-flow formulas or cells:…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.