DATEDIF in Excel: Calculate Completed Months Between Dates

Formulas & functions · Updated November 22, 2026

In Excel, use =DATEDIF(A2,B2,"m") to count the complete months between a start date in A2 and an end date in B2. The “m” unit counts only whole months, so a period that has not yet reached its next monthly anniversary is not rounded up.

What DATEDIF Means

DATEDIF is a date function that returns the difference between two dates in a chosen unit. Its name reflects that purpose: date difference. It is useful for calculations such as a customer’s full months of membership, an employee’s completed months of service, or the time between two milestones.

The function has three arguments: a start date, an end date, and a text unit that tells Excel what to return. For completed months, the unit is “m”. The start date must be earlier than or equal to the end date. If the dates are reversed, DATEDIF returns the #NUM! error.

DATEDIF is retained in Excel for compatibility with older spreadsheet programs. It may not appear in Excel’s function autocomplete or have the same level of in-app guidance as newer functions, but you can enter it directly in a cell. It is available in current Microsoft Excel, including Microsoft 365 for Windows.

How the Month Calculation Works

DATEDIF with “m” counts full calendar months that have elapsed. It does not simply subtract the month numbers, and it does not convert the number of days into an average number of months. Excel checks whether the end date has reached the corresponding day in a later month.

For example, with a start date of January 15, 2024, and an end date of April 14, 2024, the result is 2. The dates have passed the February 15 and March 15 anniversaries, but not April 15. If the end date is April 15, the result becomes 3.

With the start date in A2 and the end date in B2, the formula is =DATEDIF(A2,B2,"m"). For January 15, 2024, in A2 and April 14, 2024, in B2, it returns 2. Change B2 to April 15, 2024, and the same formula returns 3.

This means DATEDIF does not round partial months to the nearest whole month. A period of three months and one day returns 3, not 4; a period just short of three full months returns 2. This is often what people mean by “months completed,” but it may not match a policy that counts every calendar month touched.

Entering the Formula and Preparing Dates

Enter start and end dates in separate cells, then put the formula in a third cell. For a small calculation, type the formula directly. To apply it to many rows, enter it in the first result row and use Excel’s fill handle to copy it down; relative references will adjust for each row.

  1. Enter the start date in A2 and the end date in B2.
  2. Select the cell where you want the result, such as C2.
  3. Type =DATEDIF(A2,B2,"m") and press Enter.
  4. Copy the formula down for additional rows, checking that each start date is no later than its end date.

With January 15, 2024, in A2 and April 15, 2024, in B2, this formula returns 3. The cells need to contain dates Excel recognizes as date values, not text that merely looks like a date. Dates entered in a format that conflicts with your computer’s regional settings can be interpreted differently. If results seem wrong, select the cells and check the formula bar and the displayed date format.

Excel stores dates as serial numbers, which allows date formulas to calculate elapsed time. Applying a date display format to a text string does not turn that string into a genuine date. If dates came from a CSV file or another system, convert or re-enter them as dates before relying on the result.

Month-End Dates and Other Edge Cases

Month-end dates can surprise people because months have different lengths. DATEDIF follows the completed-month rule; it does not automatically treat the last day of one month and the last day of the next month as a full month in every case. For instance, January 31 to February 28 may return 0: the end date is earlier in the month than the start-date day number, so the next full monthly anniversary has not been reached under this calculation.

Leap years create a similar question for dates such as February 29. Test representative dates from your own data, particularly if a contract, service policy, or reporting rule defines a month-end anniversary differently from Excel’s calculation. A formula can be mathematically consistent and still differ from the rule an organization intends to apply.

DATEDIF also needs valid date order. If you want an absolute difference regardless of which date is earlier, first decide whether that is appropriate for your situation. Silently swapping dates can hide a data-entry error, so it is often better to flag reversed dates and correct the source data.

With a start date in A2 and an end date in B2, this check returns a message rather than passing a reversed pair into DATEDIF: =IF(B2<A2,"Check dates",DATEDIF(A2,B2,"m")). For A2 equal to April 15, 2024, and B2 equal to January 15, 2024, it returns Check dates. For a valid January 15 to April 15 pair, it returns 3.

Completed Months Versus Calendar-Month Boundaries

A common source of confusion is the difference between completed months and the number of month boundaries crossed. DATEDIF with “m” answers the first question. A date pair can cross into a new calendar month without accumulating a full month: January 31 to February 1 crosses a month boundary but does not represent one completed monthly interval in DATEDIF’s calculation.

If your task is to count calendar-month changes instead, calculate the difference in year and month numbers. With a date in A2 and another date in B2, use =12*(YEAR(B2)-YEAR(A2))+MONTH(B2)-MONTH(A2). From January 31, 2024, to February 1, 2024, it returns 1, because the dates are in adjacent calendar months. It does not check the days of the month, so it should not be described as completed months.

Choose the method based on the question being asked. Billing, benefits, and service calculations may have written rules that specify how partial months are treated. A calendar report may instead need to group dates by the month and year they fall in. Confirm the definition before selecting a formula; the word “months” alone is not precise enough to determine the intended result.

Returning Years and Remaining Months

For a human-readable age or service period, you may want full years plus the remaining full months rather than one total-month count. DATEDIF supports the “y” unit for complete years and “ym” for remaining months after complete years are excluded. The second unit does not return total months across the entire period.

With a start date in A2 and an end date in B2, use =DATEDIF(A2,B2,"y")&" years, "&DATEDIF(A2,B2,"ym")&" months". From January 15, 2020, to April 14, 2023, the formula returns 3 years, 2 months. The dates have completed three years and two additional full months; the next monthly anniversary, April 15, has not arrived.

This result is text because the formula joins numbers and words. That is convenient for a label, but less suitable if you need to perform further arithmetic on the result. Keep the numeric result in a separate cell when you need to sort, sum, or compare month counts.

Choosing the Right DATEDIF Unit

DATEDIF recognizes several unit codes. The right choice depends on whether you need complete units, a combined total, or a remainder:

  • "m" returns complete months across the whole interval.
  • "y" returns complete years.
  • "ym" returns remaining months after complete years are removed.
  • "d" returns the total number of days.
  • "yd" returns days after complete years are excluded.

For total days, for example, “d” differs from “yd”: the first counts all days in the date interval, while the second gives a day remainder that ignores whole years. The “md” unit is sometimes used for a day remainder that ignores months and years, but it can produce unexpected results in some date combinations. Avoid relying on it for important calculations; consider a clearly defined alternative or test the exact cases your work requires.

Use lowercase or uppercase unit letters consistently and enclose the unit in straight quotation marks. A misspelled unit or missing quotation mark can cause a formula error. The standard syntax is =DATEDIF(start_date,end_date,"unit"); replace the argument names with cell references or valid date expressions.

Common Errors and Troubleshooting

If a DATEDIF formula does not work as expected, check the values and the formula structure before changing the calculation. Common causes include:

  • #NUM!: the start date is later than the end date.
  • #VALUE!: one or both arguments may not be valid dates or date values.
  • A surprising smaller month count: the end date may fall before the next monthly anniversary, or the calculation may be using completed months when you wanted calendar-month boundaries.
  • A formula displayed as text: the cell may be formatted as Text, or the entry may begin with an apostrophe. Change the cell to General, then re-enter the formula.
  • Different results after importing data: a date may have been imported as text or interpreted using a different regional date order.

To diagnose a date, select its cell and inspect the formula bar. A date displayed as “3/4/2024” could mean March 4 or April 3 depending on the format used when it was entered or imported. Make the date convention explicit when sharing workbooks or loading external data. In Microsoft 365 for Windows, the cell’s number format can be inspected from Home > Number Format; formatting changes appearance, however, and does not by itself repair text dates.

Frequently Asked Questions

Does DATEDIF round up partial months?
No. With the “m” unit, it counts completed months. A period that is one day short of its next monthly anniversary remains at the previous whole-month count.

Why is DATEDIF missing from Excel’s formula suggestions?
DATEDIF is maintained for compatibility and may not appear in autocomplete or the usual function list. You can still type the formula directly, using the correct argument order and quotation marks around the unit.

Can DATEDIF calculate months between dates in different years?
Yes. The “m” unit returns total completed months across the full interval, not just the difference between the month numbers. For example, a full year and three additional months produces 15.

What is the difference between “m” and “ym”?
“m” returns all complete months in the interval. “ym” returns only the month remainder after complete years are excluded, which is useful for a years-and-months display.

Can I use DATEDIF when the end date comes first?
Not as written: a start date later than the end date produces #NUM!. Correct the dates or deliberately create a separate calculation for reverse-ordered dates, rather than assuming DATEDIF will return a negative month count.

Which Method Should You Use?

Use DATEDIF with “m” when you need the number of complete months between two correctly ordered dates. Use a year-and-month-number calculation when you need to count calendar-month boundaries, and use “y” with “ym” when you want a duration expressed as complete years and leftover months. These answers can differ for the same pair of dates because they measure different things.

Before using a month result for a policy, payment, or formal report, verify the governing definition of a month and test edge cases such as month-end dates and leap days. Clear definitions, genuine Excel date values, and a few checked examples make the calculation easier to trust and explain.

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.