DATEDIF Formula in Excel: Calculate Years and Months

Formulas & functions · Updated November 22, 2026

To calculate completed years and the remaining whole months between two dates in Excel, use =DATEDIF(A2,B2,"y") for years and =DATEDIF(A2,B2,"ym") for leftover months, assuming the start date is in A2 and the end date is in B2. For example, from January 15, 2020 to March 3, 2024, the result is 4 years and 1 month.

DATEDIF is useful for ages, service periods, and other date intervals where you want whole units rather than a decimal fraction. The important distinction is that the “y” unit returns completed years, while “ym” returns completed months left after those years have been counted.

How DATEDIF calculates years and months

DATEDIF takes a start date, an end date, and a unit code. Excel compares the dates and returns the number of complete units that fit between them. It does not simply subtract the year numbers or month numbers shown in the cells. That makes it more appropriate than subtracting years when the dates do not fall on the same anniversary.

With start date January 15, 2020 in A2 and end date March 3, 2024 in B2, fewer than four full years would have elapsed if the end date were January 14, 2024. But March 3 is after the January 15 anniversary, so four full years have elapsed. The next anniversary is still in the future, and only one complete month has passed since February 3.

  • "y" returns completed years.
  • "ym" returns completed months remaining after complete years are excluded.
  • "m" returns the total number of completed months, including the months represented by complete years.

Enter the dates as real Excel dates

Before using the formula, make sure A2 and B2 contain dates Excel recognizes as dates, not text that merely looks like a date. Type dates in a format Excel accepts for your regional settings, or create them with Excel’s date tools. You can check a cell by changing its number format: a real date will usually display as a number if you select Home > Number Format and choose General.

On Microsoft 365 for Windows, the formula can go in C2 or another empty cell. If A2 contains January 15, 2020 and B2 contains March 3, 2024, Excel returns 4. The formula bar begins with an equals sign; enter the unit in straight quotation marks. Function names and formula behavior are the same in Excel for Mac and Excel for the web, though the ribbon layout and date-entry interpretation can vary by platform and region.

Assuming A2 is the start date and B2 is the end date, use this formula to return completed years:

=DATEDIF(A2,B2,"y")

For the example dates, this returns 4. If the end date is before the start date, DATEDIF returns an error rather than a negative duration, so check the order of the dates when setting up the sheet.

Return years and leftover months together

To display both parts in one cell, join the two DATEDIF results with text. With the start date in A2 and end date in B2, the following formula also adjusts “year” and “month” to singular when the corresponding value is 1:

=DATEDIF(A2,B2,"y")&" "&IF(DATEDIF(A2,B2,"y")=1,"year","years")&", "&DATEDIF(A2,B2,"ym")&" "&IF(DATEDIF(A2,B2,"ym")=1,"month","months")

For January 15, 2020 through March 3, 2024, the displayed result is 4 years, 1 month. This is text, not a numeric duration: use the separate numeric results if you need to add, average, or compare the values later. For a report, you can keep years and months in separate columns and combine them only in a presentation column.

The “ym” unit does not return all months in the interval. It returns only the complete months remaining after full years are removed. A result of 4 years and 1 month therefore does not mean the whole interval is just one month; it means four completed years plus one additional completed month.

When to use “m” instead of “ym”

Use “m” when you need the total number of completed months from the start date. With the same January 15, 2020 start date in A2 and March 3, 2024 end date in B2, the formula returns 49 completed months. The arithmetic matches the split result: four years account for 48 months, plus one remaining completed month equals 49.

Assuming A2 contains the start date and B2 the end date, calculate total completed months with:

=DATEDIF(A2,B2,"m")

This is useful for counting full monthly periods in a record or checking a duration expressed in months. It is not interchangeable with “ym”: for the example, “ym” returns 1, while “m” returns 49. Neither unit returns a decimal month or estimates partial months as a fraction.

Choosing the right unit for your calculation

DATEDIF supports several unit codes, but each answers a different question. Choose the unit that matches how you plan to use the result:

  • “y”: completed years, such as whole years of age or service.
  • “m”: total completed months across the full interval.
  • “d”: total days between the dates.
  • “ym”: remaining completed months after complete years are excluded.
  • “yd”: days remaining after complete years are excluded.
  • “md”: a day difference that ignores months and years; it can produce confusing results in some date combinations, so avoid relying on it for important calculations.

For a duration written in years and months, the usual pair is “y” and “ym”. For a total number of months, use “m”. If your question concerns calendar boundaries—for example, how many January-to-January periods appear in a reporting range—clarify that requirement first, because counting calendar months touched is different from counting fully completed months.

Examples and date-boundary cases

Suppose A2 contains April 10, 2022 and B2 contains April 9, 2023. The year result is 0 because the first anniversary has not arrived. If B2 is changed to April 10, 2023, the year result becomes 1. This shows why subtracting the year numbers would be misleading: both end dates are in 2023, but only one reaches a full year.

Month-end dates can also produce results that differ from casual calendar counting. For example, a start date of January 31 and an end date of February 28 may not count as one completed month under DATEDIF’s full-month calculation, because the matching day of the month has not been reached. Someone who means “one calendar month later” may prefer a different rule. State the intended convention before using date results for payroll, eligibility, billing, or other decisions.

Leap-day dates deserve the same care. A period beginning February 29 may have a different perceived anniversary in a non-leap year, depending on whether your rule treats February 28 or March 1 as the anniversary. DATEDIF follows Excel’s date calculation rules; it does not know an organization’s policy. Confirm the rule that applies to your situation rather than assuming every anniversary convention is universal.

Common errors and troubleshooting

  • #NUM!: Check whether the start date is later than the end date. DATEDIF expects the dates in chronological order.
  • #VALUE! or an unexpected answer: Check whether either cell contains text instead of a valid date. Imported data often includes text dates or extra spaces.
  • A formula appears as text: The result cell may be formatted as Text, or the entry may begin with an apostrophe. Change the cell to General, then re-enter the formula.
  • The result changes by region: Dates such as 4/5/2024 can mean different days in different locales. Use an unambiguous entry method or verify the displayed date after entry.
  • DATEDIF is missing from suggestions: Excel may not offer DATEDIF in the usual function autocomplete, but the function can still work when entered directly with valid arguments.

If your data may contain reversed or incomplete date ranges, add a check before calculating rather than hiding an error. For a worksheet where A2 is the start date and B2 is the end date, this formula displays a prompt when the end date is earlier:

=IF(B2<A2,"Check dates",DATEDIF(A2,B2,"y"))

For dates in the correct order, this returns the completed-year count; for a reversed interval, it displays “Check dates.” A blank-cell check may also be useful in a larger sheet so that unfinished rows do not show misleading results.

DATEDIF compared with other approaches

DATEDIF is convenient when the desired result is a whole-year, whole-month, or whole-day count. It is also a familiar choice in age and tenure worksheets. Its drawbacks are that its unit codes are not descriptive words, it is not always suggested while typing, and edge cases require the user to decide what counts as an anniversary or complete month.

For elapsed time expressed as a decimal number of years, YEARFRAC is an alternative, but it answers a different question. A decimal year can be useful for some analyses, yet it is not the same as a readable “years and remaining months” result. For ages in whole years, DATEDIF with “y” is usually clearer. For a calendar schedule, functions such as EDATE may be more suitable when the task is to move a date forward or backward by a specified number of months, rather than measure an interval.

There is no universal best method for every date problem. Decide whether you need completed anniversaries, complete months, a decimal duration, or calendar-month counting. Then use the calculation whose definition matches that need, and document the convention when other people will rely on the spreadsheet.

Frequently Asked Questions

What is the DATEDIF formula for years and months in Excel?
With the start date in A2 and end date in B2, use =DATEDIF(A2,B2,"y") for completed years and =DATEDIF(A2,B2,"ym") for the remaining completed months. Combine both results if you want a single display.

What is the difference between “m” and “ym” in DATEDIF?
“m” returns total completed months in the whole interval. “ym” returns the completed months left after complete years are taken out. An interval of 4 years and 1 leftover month therefore has 49 total completed months and 1 month returned by “ym”.

Why does DATEDIF return zero years?
The end date may fall before the start date’s next anniversary. For example, April 9, 2023 is one day short of a full year after April 10, 2022, so the completed-year result is 0.

Can DATEDIF calculate age in years and months?
Yes. Put a birth date in the start-date cell and the date to calculate age on in the end-date cell, then use the “y” and “ym” results. If you want age as of today, use a date that updates automatically, while remembering that any future birth date or reversed date pair will cause an error.

Why can’t I find DATEDIF in Excel’s function list?
DATEDIF is a supported compatibility function, but it may not appear in autocomplete or the function list like many other functions. Type it directly with its start date, end date, and quoted unit code.

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.