Microsoft Excel’s DATEDIF function calculates the difference between two dates in complete years, months, or days, or in combinations such as years and remaining months. Use the syntax =DATEDIF(start_date,end_date,unit); 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.
What DATEDIF does
DATEDIF is useful when you need elapsed calendar time rather than just the number of days separating two dates. Common uses include calculating someone’s age on a particular date, measuring an employee’s completed service, or reporting how long a project has been underway. Its result depends on the unit you request: a difference of 25 months can be reported as 2 complete years, 25 total months, or the total number of days.
The function is available in Microsoft 365 for Windows, but it is a legacy compatibility function: it was retained to support workbooks created in older spreadsheet programs. Excel may not show DATEDIF in formula autocomplete or the Insert Function list. That does not mean the function is unavailable; you can type it directly into a cell.
DATEDIF returns a number, not a formatted sentence. You can format the result as a number or combine it with text if you want a label. Think of it as a way to count complete calendar intervals, not as a general-purpose measure of time or a decimal fraction of a year.
Syntax and unit codes
The function has three arguments: the starting date, the ending date, and a unit code in quotation marks. The start date must be earlier than or equal to the end date. The unit is not case-sensitive, but it must be one of the supported codes below.
"Y"counts complete years between the dates."M"counts complete months in total, rather than years plus leftover months."D"counts the total number of days between the dates."YM"counts leftover months after complete years are excluded."YD"counts days after complete years are excluded."MD"counts days after complete months and years are excluded. Microsoft warns that this unit can produce incorrect results in some situations.
Use straight quotation marks around the unit, and separate arguments with commas in standard English-language Excel settings. If your regional settings use another list separator, Excel may display or require a different separator. The unit code itself still needs to be quoted.
How to enter a DATEDIF formula
Put genuine Excel dates in two cells, then enter the function in a third. For instance, enter a start date in A2 and an end date in B2. Select a result cell, type the formula, and press Enter. On Windows, Ctrl+; inserts the current date as a fixed value; on Mac, the equivalent shortcut is Control+;. A fixed date is useful for a record that should not change later.
For a simple example, suppose A2 contains March 15, 2020, and B2 contains August 20, 2023. With those dates in A2 and B2, this formula returns 3 because three complete anniversaries have passed: =DATEDIF(A2,B2,"Y"). With the same cell layout, this formula returns 41 complete months: =DATEDIF(A2,B2,"M"). With that same layout, this formula returns 1,253 elapsed days: =DATEDIF(A2,B2,"D").
The remaining units describe the interval after larger units are removed. With March 15, 2020 in A2 and August 20, 2023 in B2, =DATEDIF(A2,B2,"YM") returns 5, the leftover months beyond three complete years. With the same layout, =DATEDIF(A2,B2,"YD") returns 158 days after excluding complete years. These component values can be useful for a years-and-months summary, but treat MD cautiously rather than assuming every component unit is equally dependable.
Combine years, months, and days
A common request is an age or tenure shown as separate years, months, and days. You can join several DATEDIF results with text. This is convenient for a display, but it inherits the limitations of each unit, especially MD. For a display that uses the example dates above, 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 first two components return 3 years and 5 months for those dates; the MD component returns 5 days in this particular example. Do not assume that the days component will be reliable for every pair of dates. If the result is intended for a formal policy, contract, or deadline, define the counting convention first and verify the calculation against that convention.
For an age that updates automatically, use TODAY() as the end date. With a birth date in A2, the formula =DATEDIF(A2,TODAY(),"Y") returns completed years as of the current date. TODAY() is a volatile date function: Excel recalculates it, so the displayed age can change when the calendar date changes. If you need a stable historical calculation, put the intended as-of date in B2 instead of using TODAY().
Choosing the right unit
Pick a unit according to what the answer is meant to represent. “How many days have elapsed?” calls for D. “How many whole billing months have passed?” may call for M, but only if your billing rules define a month using the same anniversary-based logic. “How old is this person in completed years?” usually calls for Y. A unit that sounds similar is not necessarily interchangeable: 24 complete months and 2 complete years often agree, but the date boundaries and intended reporting convention still matter.
- Use Y for completed anniversaries, not for a rounded or fractional age.
- Use M for all complete months across the interval; it does not return a years-and-months pair.
- Use D when you want the actual number of elapsed days, including days in leap years.
- Use YM or YD only when you specifically need the remainder after full years are removed.
- Avoid MD for sensitive calculations unless you have checked the behavior for the date ranges that matter.
Calendar months are not equal-length blocks of 30 days. For example, a month-end date followed by a date in the next month can raise questions about what counts as a complete month. DATEDIF applies its own calendar-based behavior; it does not let you choose a business-day calendar, a 30-day-month convention, or a custom rule for end-of-month anniversaries. If a workplace or service agreement defines a different rule, implement that rule explicitly rather than treating DATEDIF as a policy decision.
Common errors and date pitfalls
If the start date is later than the end date, DATEDIF returns #NUM!. Check that the dates are in the intended order; swapping the cells may fix the issue if the interval was simply entered backwards. An invalid unit code, or a unit entered without quotation marks, can produce #NUM! or a formula error. Make sure the code is exactly Y, M, D, YM, YD, or MD.
Excel stores dates as serial numbers. A value that looks like a date may actually be text, particularly if it was imported from another system or uses a different regional date format. Text dates can cause unexpected results or errors. Check whether Excel recognizes the cell as a date by changing its number format or using it in a simple date calculation. Re-entering the date in a recognized format or converting the imported column may be necessary.
Also check for hidden time values. A cell can contain both a date and a time, even when its display format shows only the date. DATEDIF is intended for date intervals, so fractional-day values or imported timestamps can make a result seem surprising. If the task concerns whole calendar dates, clean or normalize the source values before calculating and confirm the dates displayed in the formula’s input cells.
Leap days and month ends deserve extra care. A year containing February 29 has an extra day, while other years do not; D counts actual elapsed days rather than assuming 365 days per year. The behavior of “one year later” for a February 29 date may also depend on the date convention you need. Test edge cases that are material to your work, such as February 29 birthdays, dates on the 30th or 31st, and intervals ending at month-end.
DATEDIF compared with subtraction and other functions
Subtracting one date from another is the simplest option when you only need elapsed days. Excel dates are stored numerically, so subtracting the earlier date from the later one returns the number of days between them. With the start date in A2 and the end date in B2, =B2-A2 returns 1,253 for March 15, 2020 through August 20, 2023. This is equivalent to the DATEDIF D result for those date-only values, but subtraction does not directly express complete calendar years or months.
YEARFRAC is a different function: it estimates a fraction of a year between two dates, with the result affected by its day-count basis argument. That is not the same as counting complete anniversaries, so it is not a direct substitute for DATEDIF with Y. Similarly, YEAR, MONTH, and DAY can extract parts of a date, but manually subtracting those parts needs extra logic for borrowing days or months. Choose the method that matches the question rather than comparing outputs that measure different things.
If you need working days rather than calendar days, DATEDIF is not the right function: weekends and holidays are included in its elapsed-day count. Excel’s NETWORKDAYS function can count working days between dates, and NETWORKDAYS.INTL allows weekend patterns to be specified. Those functions address a different question, so confirm whether your schedule counts calendar time, business days, or a defined service period before selecting one.
Frequently Asked Questions
Why can’t I find DATEDIF in Excel’s function list?
DATEDIF is a legacy compatibility function and may not appear in autocomplete or the Insert Function dialog. Type its name and arguments directly into a cell. In Microsoft 365 for Windows, the formula remains usable even when Excel does not suggest it.
Does DATEDIF calculate age correctly?
DATEDIF with the Y unit counts completed years between a birth date and an as-of date. It can be appropriate for a straightforward completed-years age, but decide how leap-day birthdays and the as-of date should be handled when precision matters. For an age that must remain fixed for a record, use a stored as-of date rather than TODAY().
What is the difference between M and YM?
M returns the total number of complete months across the entire interval. YM returns only the months remaining after complete years are excluded. For an interval of 3 years and 5 months, M is 41 and YM is 5.
Why does DATEDIF return #NUM!?
The most common cause is that the start date comes after the end date. An unsupported unit code can also cause an error. Check the date order, confirm both inputs are valid Excel dates, and use a quoted unit such as “Y” or “D”.
Can I use DATEDIF in Excel for the web or on Mac?
DATEDIF formulas are generally supported in current Excel versions, including Microsoft 365 on Mac and Excel for the web, though menus and formula assistance can differ from Windows. You can enter the formula directly in a cell. Regional settings may also affect the argument separator Excel expects.
Practical checks before relying on a result
Before using a DATEDIF output in a report, verify that both inputs are recognized dates, the start and end are in the correct order, and the selected unit answers the intended question. Try a few boundary examples from your data, especially dates around month-end and leap day. For an interval that must follow a written rule, compare the spreadsheet result with that rule rather than assuming the function defines what counts as a year or month. Once the logic is confirmed, label the result clearly—for example, “complete years” or “elapsed calendar days”—so readers do not mistake one measure for another.
Related
- Master Excel Custom Number Formats: A Practical Guide
- Master List Excel Template: Create, Organize, and Use It
- Master Contact List Excel Template: Build and Manage It
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Can’t Filter in Excel? Causes, Fixes, and WorkaroundsYes—Excel can filter a list or table, but the Filter command may be unavailable, the dropdown arrows may…
- COUNTIF Excel by Text Match: Exact and Partial MatchesYes. In Excel, use COUNTIF to count cells that match text: with the values in A2:A9, =COUNTIF(A2:A9,"Apple") counts…
- INDEX MATCH With Two MATCH Functions: Practical ExamplesYes: the standard two-way lookup uses INDEX with one MATCH to find a row and a second MATCH…
- Power Query XLOOKUP From Another Table: How It WorksPower Query does not run the Excel XLOOKUP function inside a query. To return columns from another table,…
- Filter Multiple Numbers in Excel: Exact and Range OptionsTo filter several exact numbers in Excel, open the column’s filter menu and select the numbers you want…
- How to Close the Formula Bar in Excel on Any DeviceTo close—or hide—the formula bar in Excel, open the View tab and clear the Formula Bar checkbox. In…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.