DATEDIF is available in Excel for Microsoft 365 on Windows, but it is a hidden compatibility function: it may not appear in autocomplete or the Insert Function list. You can still use it by typing the formula directly into a cell, such as =DATEDIF(A2,B2,"Y") to count complete years between dates in A2 and B2.
Why DATEDIF Seems to Be Missing
DATEDIF is a real Excel function, despite its absence from many function menus and help prompts. Microsoft retains it for compatibility with older spreadsheet programs, and the function has historically been left undocumented in some Excel interfaces. That unusual status explains why typing “DATEDIF” may not produce the usual suggestion or argument guide. It does not mean the function is unavailable or needs to be installed.
Enter the function manually, including its opening parenthesis, arguments, and closing parenthesis, then press Enter. Excel can calculate it even if it does not offer the name in its autocomplete list. The behavior is the same in current Microsoft 365 Excel for Windows and Mac; Excel for the web also supports DATEDIF, although editing and interface details can differ. If a workbook is opened in another spreadsheet application, check that application’s support separately.
DATEDIF is useful when you specifically need elapsed years, months, or days between two dates. It is not a general-purpose date formatter, and some of its less common units can produce surprising results. Knowing which unit to choose matters as much as knowing how to enter the function.
How the Function Works
The function takes three arguments: a start date, an end date, and a quoted text code called a unit. The dates can be cell references or valid Excel date values. The start date must be earlier than or equal to the end date; otherwise, Excel returns an error. For ordinary use, put the earlier date in the first cell and the later date in the second.
For the examples in this article, assume A2 contains the actual Excel date January 15, 2022, and B2 contains November 3, 2024. These should be date values, not text that merely looks like a date. If necessary, enter dates using a format Excel recognizes in your regional settings, then apply a date number format through Home > Number.
With the start date in A2 and the end date in B2, the basic pattern for complete years is =DATEDIF(A2,B2,"Y"), which returns 2 for these example dates. The unit must be enclosed in straight double quotation marks. The function name and unit are not case-sensitive, but using uppercase unit codes makes formulas easier to scan.
What Each DATEDIF Unit Counts
DATEDIF supports six unit codes. Their results are not interchangeable: some count complete units, while others report the remainder after larger units have been removed.
"Y"counts complete years. With A2 and B2 as in the example, it returns 2."M"counts complete months across the whole interval, not just the months left over after complete years. For the example dates, it returns 33."D"counts elapsed days. For January 15, 2022 through November 3, 2024, it returns 1,023."YM"returns the remaining months after complete years are excluded. For the example dates, it returns 9."YD"returns the number of days after complete years are excluded. It returns 293 for the example dates."MD"returns the remaining days after complete months and years are excluded. It returns 19 for the example dates, but Microsoft warns that this unit can produce incorrect results in some situations.
With A2 holding January 15, 2022, and B2 holding November 3, 2024, the formula =DATEDIF(A2,B2,"M") returns 33 complete months. It does not return 9, because 9 is the leftover month count after the two complete years are taken out. The formula =DATEDIF(A2,B2,"D") returns 1,023 elapsed days. Subtracting the date cells directly also returns 1,023 for these date-only values, so DATEDIF is not necessary when days are all you need.
Using DATEDIF to Calculate Age
A common reason people search for the function is to calculate a person’s age in completed years. Put the birth date in A2 and the date to calculate age on in B2. The formula =DATEDIF(A2,B2,"Y") returns 20 if A2 is March 10, 2004, and B2 is March 9, 2025: the person has not yet reached their birthday in 2025. If B2 is March 10, 2025, the same formula returns 21.
Using a cell for the calculation date is often preferable to typing a date into the formula. It makes the answer reproducible and lets you calculate age on a past or future date. If you want age as of today, use A2 for the birth date and replace B2 with the current-date function: =DATEDIF(A2,TODAY(),"Y"). Here, the formula assumes the birth date is a valid Excel date in A2; it returns the number of completed years as of the day the workbook recalculates.
For someone born on February 29, the intended birthday in a non-leap year can depend on the policy or rule being applied. DATEDIF supplies a calculation, but it cannot decide which birthday convention is appropriate for a legal, organizational, or personal purpose. Confirm the rule you need before relying on a result in a situation where that distinction matters.
Choosing Between Total Months and Years Plus Months
The most common misunderstanding is treating the total-month unit and the leftover-month unit as if they mean the same thing. For the example dates, the total number of complete months is 33, while the interval can also be described as 2 complete years and 9 additional complete months. The first question to ask is whether the report needs one total or a split into units.
With A2 containing January 15, 2022, and B2 containing November 3, 2024, use =DATEDIF(A2,B2,"YM") to return 9 leftover months after complete years are removed. To display the years and remaining months together, you can join the two calculations with text. The following formula assumes the same dates in A2 and B2 and returns “2 years, 9 months”:
=DATEDIF(A2,B2,"Y")&" years, "&DATEDIF(A2,B2,"YM")&" months"
This wording describes completed units, not a rounded decimal duration. A result of 2 years and 9 months does not mean the interval is exactly 2.75 years: the length of a year and the length of calendar months vary. For time spans that need a decimal number of years, a different method may be more appropriate, and the chosen day-count convention should be stated.
Common Errors and Date Problems
If DATEDIF returns an error or an unexpected answer, check the underlying dates before changing the formula. A cell can display something date-like while storing text, and date interpretation can vary with regional settings. Select the cell and check its value and number format; converting text to a true date may be necessary before the calculation works.
- The end date is earlier: DATEDIF requires the start date to be no later than the end date. If the order can vary, decide how the workbook should handle reversed dates rather than assuming DATEDIF will silently swap them.
- A date is stored as text: Re-enter it as a recognized date or convert it with an appropriate method. Changing the display format alone does not necessarily convert text into a date value.
- The result is one month lower than expected: DATEDIF counts complete months. For example, a period from January 31 to February 28 may not count as one complete month under that rule, because the later date has not reached the corresponding day number.
- The MD result seems odd: The MD unit has documented edge cases and is not a dependable substitute for carefully defined calendar arithmetic. For business-critical calculations, test relevant boundary dates and consider a clearer formula or method.
- The formula is displayed instead of calculated: Check whether the cell is formatted as Text, whether the formula begins with an apostrophe, or whether Show Formulas is enabled. Change the cell to General if needed, then re-enter the formula.
Excel stores dates as serial numbers, which is why subtracting one date cell from another can produce elapsed days. The workbook’s date system and the way imported data was converted can affect date values, so avoid mixing text dates, numbers, and dates from external systems without checking them. Formatting controls how a value looks; it does not, by itself, guarantee that the stored value is a valid date.
Alternatives When DATEDIF Is Not the Best Fit
Use the simplest calculation that matches the question. For elapsed days between two date-only values in A2 and B2, subtraction is direct: =B2-A2. With the example dates, it returns 1,023. If the task is to show a date as “March 2025” or display a year, use a cell’s number format or the TEXT function rather than asking DATEDIF to create a formatted date label.
For a decimal year fraction, Excel’s YEARFRAC function offers a day-count basis argument, so its result depends on which basis you select. That makes it useful in some analytical settings, but it is not automatically equivalent to completed years from DATEDIF. Do not substitute one for the other without checking what the calculation is meant to represent.
For inclusive day counts, first define whether both endpoints should count. DATEDIF with the day unit gives the elapsed difference: January 1 to January 2 is one day, not two. If a specific process counts both the start and end dates, the calculation needs to reflect that convention. Write the rule down, especially when dates feed reports, schedules, or eligibility checks.
Frequently Asked Questions
Is DATEDIF available in Excel for Microsoft 365?
Yes. It works in Microsoft 365 Excel, even though the function may not appear in autocomplete or the Insert Function list. Type it directly into a cell.
Why does DATEDIF not show up when I type it?
Excel does not consistently expose this legacy compatibility function through its function suggestions. Its absence from the list does not mean the formula is unsupported; enter the function name and arguments manually.
What is the formula for age in years in Excel?
If A2 contains the birth date and B2 contains the date to calculate age on, use =DATEDIF(A2,B2,"Y"). It counts completed years, so it does not increase until the relevant birthday has been reached.
What is the difference between Y and YM?
The Y unit returns complete years. YM returns the remaining months after those complete years are removed. For a span of 2 years and 9 months, Y returns 2 and YM returns 9.
Does DATEDIF count the start date?
The D unit measures the elapsed difference between date values. January 1 to January 2 returns one day. If your process requires an inclusive count of both endpoints, apply the counting rule explicitly rather than assuming DATEDIF is inclusive.
Can I use DATEDIF in Google Sheets?
Google Sheets also supports DATEDIF, but this article’s interface instructions refer to Excel. If you move a workbook between programs, check the formulas and date handling in the destination application, particularly for unusual units and boundary dates.
Related
- Commission Calculator Excel Template: Build One That Fits
- Redo in Excel: Keyboard Shortcuts and How It Works
- Daily Vehicle Checklist Template Excel: What to Include
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula for Highest Value: MAX and Other OptionsTo find the highest value in Excel, use the =MAX(B2:B20) formula, assuming the numbers you want to compare…
- Excel LAMBDA Formula Examples for Reusable FunctionsExcel LAMBDA lets you turn a calculation into a reusable function, so you can name it once and…
- Create a Sequence of Dates in Excel: Formulas and FillTo create a sequence of dates in Excel, enter a starting date and use =SEQUENCE(10,1,DATE(2026,1,1),1) to spill ten…
- Excel Formulas to Find Matching Data in Your ListsTo check whether a value appears in another Excel list, use COUNTIF; to return information from the matching…
- Find Matching Names in Two Excel Columns With FormulasTo find names in column A that also appear in column B, enter =IF(COUNTIF($B$2:$B$100,A2)>0,"Match","Not found") beside the first…
- Caleb Hammer Budget Spreadsheet: How to Find the Right OneIf you’re looking for a Caleb Hammer budget spreadsheet, verify any download through Caleb Hammer’s official website or…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.