Yes—DATEDIF is a working Excel function, even though Excel may not suggest it as you type or list it with other functions. Enter it manually as =DATEDIF(start_date,end_date,unit) to calculate the number of complete years, months, or days between two dates.
Why DATEDIF Seems to Be Missing
DATEDIF is an unusual but legitimate Excel function. It has been retained for compatibility with Lotus 1-2-3 spreadsheets, and Microsoft’s documentation describes it as a function for calculating the difference between two dates. Its long history helps explain why it can feel less visible than functions such as SUM or YEAR.
In Microsoft Excel for Microsoft 365 on Windows, DATEDIF may not appear in formula autocomplete as you type, and it may not be offered in the Insert Function list. That absence does not mean the function is disabled or unavailable. Type the function name and its arguments directly into a cell or the formula bar, then press Enter. Excel should calculate it if the dates and unit are valid.
DATEDIF is not an add-in, a macro, or a feature that needs to be switched on. It is also not the same as a typo such as “DATEIF.” The spelling has no second “F” after DATE: the function is DATEDIF. The same basic formula is available in current desktop Excel and Excel for the web, although menu locations and formula entry experiences can differ slightly between apps.
Syntax and What the Arguments Mean
The syntax is =DATEDIF(start_date,end_date,unit). The first two arguments are dates, and the third is a quoted text code telling Excel what kind of difference to return. The start date must be earlier than or equal to the end date.
For example, with a start date in A2 and an end date in B2, use =DATEDIF(A2,B2,"Y") to return the number of complete years between them. The quotation marks around the unit are required. Cell references are usually safer than typing date text inside a formula, because Excel can interpret typed dates differently depending on regional settings.
DATEDIF returns a number, not a specially formatted date. If a result looks like a calendar date, select the result cell and choose Home > Number Format, then select General or Number. The function does not change the original date values.
What Each DATEDIF Unit Returns
The unit controls whether Excel counts complete years or months, or returns a day difference. Use uppercase letters for clarity; Excel does not require the unit to be uppercase.
"Y"returns the number of complete years between the dates. It does not count a year that has not yet been completed."M"returns the number of complete months between the dates."D"returns the total number of days between the dates."YM"returns the number of remaining complete months after whole years have been excluded."MD"returns the number of remaining days after whole months and years have been excluded. Microsoft warns that this unit can produce inaccurate results in some situations, so use it cautiously."YD"returns the number of days between the dates as if they were in the same year, ignoring whole years.
These units answer different questions. “How many complete months have passed?” calls for "M"; “how many months remain after counting complete years?” calls for "YM". The latter is often useful when displaying an elapsed time as years and leftover months, but it is not the total number of months.
Worked Example: Calculate Years, Months, and Days
Suppose A2 contains January 15, 2020, and B2 contains March 20, 2024, with both cells stored as real Excel dates. The following formulas show different ways to describe the same interval:
- With the dates in A2 and B2,
=DATEDIF(A2,B2,"Y")returns 4 complete years. - With the dates in A2 and B2,
=DATEDIF(A2,B2,"M")returns 50 complete months. - With the dates in A2 and B2,
=DATEDIF(A2,B2,"D")returns 1,526 days. - With the dates in A2 and B2,
=DATEDIF(A2,B2,"YM")returns 2 remaining complete months after the four whole years. - With the dates in A2 and B2,
=DATEDIF(A2,B2,"MD")returns 5 remaining days for this example. Because MD has known edge-case risks, do not assume it will behave as expected for every pair of dates.
The results are not competing answers: each one measures a different unit. The total of 1,526 days includes leap-day effects in the actual date interval. The “4 years, 2 months, 5 days” style is a component breakdown, not a conversion of every day into a fixed 365-day year and 30-day month.
Using DATEDIF to Calculate Age
A common use is calculating someone’s age in completed years. If A2 contains the person’s birth date and B2 contains the date on which you want to calculate their age, enter =DATEDIF(A2,B2,"Y"). For example, a person born on July 10, 2000, is 23 on July 9, 2024, and 24 on July 10, 2024. DATEDIF counts completed anniversaries rather than simply subtracting the birth year from the current year.
To calculate age as of today, with the birth date in A2, use =DATEDIF(A2,TODAY(),"Y"). TODAY updates when Excel recalculates the workbook, so the displayed age can change as the date changes. If you need a historical or fixed reporting date, use a date in a cell instead of TODAY so that the result remains tied to that chosen date.
For a worksheet where dates may be missing, a guarded version can prevent a calculation until both date cells contain values. With the start date in A2 and end date in B2, use =IF(OR(A2="",B2=""),"",DATEDIF(A2,B2,"Y")). Check that both cells contain valid dates and that the start date is not later than the end date; the blank-cell check does not make an invalid date range valid.
Common Errors and Date Problems
DATEDIF can return an error or a surprising result when the input cells are not genuine Excel dates. Dates imported from another system may be stored as text even if they look like dates. If a calculation fails, check whether Excel recognizes each value as a date: changing the cell’s number format alone does not convert text into a date. Re-entering the date in a recognized format or converting the source data may be necessary.
- #NUM! commonly appears when the start date is later than the end date, or when the dates are otherwise outside the valid range for the calculation. Swap the arguments only if that matches the question you intend to answer.
- #VALUE! can occur when an argument cannot be interpreted as a valid date or when the unit is not one of the supported codes.
- A result of zero for
"Y"or"M"can be correct: it means the interval has not reached a complete year or month. - A strange number or display can be a cell-format issue. DATEDIF returns a count; set the result cell to General or Number rather than a date format.
Also consider whether the dates include times. DATEDIF is designed for date intervals, not precise elapsed hours or minutes. If your values contain times and you need a duration, subtract the date-time values directly and format the result appropriately; a result measured in days can be multiplied by 24 to express hours. For ordinary calendar age or completed months, use date-only values whenever possible.
DATEDIF Compared With Other Date Calculations
For a total number of days, simple subtraction is often easier to read. With the start date in A2 and end date in B2, =B2-A2 returns 1,526 for the example dates above, provided the cells hold valid Excel dates. DATEDIF with the "D" unit is also suitable when you want the formula to state explicitly that you are calculating a date difference.
For complete years or months, subtraction of dates does not directly provide the same answer. Subtracting the year numbers, for instance, can overstate someone’s age if their birthday has not occurred yet in the current year. DATEDIF’s "Y" unit handles that completed-anniversary distinction. For reports that need a fractional year rather than complete years, Excel’s YEARFRAC function may be a better fit, but its result depends on the chosen day-count basis and is not the same as an age in whole years.
Choose the calculation based on the question: total days, completed months, completed years, or a fractional year. If the report needs a human-readable duration such as “4 years, 2 months, and 5 days,” calculate the components separately and combine them only after deciding how to handle month-end dates and the MD unit’s limitations.
Frequently Asked Questions
Is DATEDIF available in Excel for Microsoft 365?
Yes. It works in Microsoft 365, even though it may not appear in autocomplete or the usual function list. Type the formula manually and use valid date arguments and a supported unit.
Why does Excel not suggest DATEDIF when I type it?
DATEDIF is a legacy compatibility function and is not surfaced like many newer functions in Excel’s suggestions. The missing suggestion is a user-interface limitation, not proof that the formula cannot calculate.
Can DATEDIF calculate a person’s age?
Yes. Put the birth date first and the date for the age calculation second, then use the "Y" unit. For age today, use TODAY() as the second argument; for a fixed date, reference a cell containing that date.
What is the difference between “M” and “YM”?"M" returns all complete months in the interval. "YM" returns only the remaining complete months after whole years have been removed, so it is useful for the month component of a years-and-months breakdown.
Why does DATEDIF return #NUM!?
First check that the start date is not later than the end date and that both inputs are valid Excel dates rather than text. Also confirm that the unit is one of the supported codes: Y, M, D, YM, MD, or YD.
Practical Takeaway
DATEDIF is in Excel, but it is easy to mistake its lack of autocomplete support for nonexistence. Enter =DATEDIF(start_date,end_date,unit) directly, select the unit that matches the question, and verify that the cells contain real dates in chronological order. For routine age and complete-month calculations it is useful; for MD-based breakdowns or durations involving times, check the result against the specific rules your report requires.
Related
- Construction Project Timeline Template Excel: Build One
- Excel Conditional Formatting: Find Cells That Contain Text
- Construction Project Cash Flow Template Excel Guide
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- VLOOKUP Between Sheets Not Working? How to Fix ItIf VLOOKUP between sheets is not working, check that the lookup value is in the first column of…
- Lookup Value Not Working in Excel? Fix the CauseA lookup value usually fails in Excel because the lookup and source values do not match exactly, the…
- Excel Text to Columns Date Not Working? Fix ItIf Excel Text to Columns is not converting your dates, run the wizard again and, in its final…
- VLOOKUP Not Working Between Workbooks? How to Fix ItWhen VLOOKUP fails between Excel workbooks, check the external reference, use an exact match, and confirm that the…
- Excel Autofit Not Working? Fix Width and Height IssuesIf Excel AutoFit is not working, select the affected columns or rows and choose Home > Format >…
- Excel Solver Not Opening? Fix the Add-InIf Excel Solver is not opening, enable its add-in in desktop Excel: go to File > Options >…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.