Excel Date Difference in Months: Formulas That Work

To find the number of complete months between two dates in Excel, use =DATEDIF(A2,B2,"m"), with the earlier date in A2 and the later date in B2. This returns whole months that have elapsed; if you instead want the number of calendar-month boundaries crossed, use a different calculation.

What “difference in months” can mean

There is not one universal month-difference answer for every pair of dates. A month can mean a completed period from one day of a month to the corresponding day in another month, a change in the month shown on a calendar, or an estimated fraction of an average month. Excel results depend on which interpretation you need.

For example, from January 15 to February 14, one complete month has not elapsed, even though the dates are in different calendar months. From January 15 to February 15, one complete month has elapsed. A calendar-boundary count, by contrast, treats January 31 to February 1 as one month change. Neither interpretation is automatically wrong; they answer different questions.

Before choosing a formula, consider what the result will represent. Billing periods, service anniversaries, and age calculations usually need completed months. A report comparing monthly dates may need calendar-month boundaries. An estimate of elapsed time may call for a decimal, with an explicitly chosen average month length.

Use DATEDIF for completed months

DATEDIF is Excel’s straightforward option when you want the number of whole months between two dates. With the earlier date in A2 and the later date in B2, enter:

=DATEDIF(A2,B2,"m")

The “m” unit means complete months, not the number of different month names touched by the interval. For dates January 15, 2024, in A2 and March 14, 2024, in B2, the result is 1. Change B2 to March 15, 2024, and the result is 2. The second month is complete only on the 15th.

DATEDIF is a compatibility function that remains available in Microsoft 365, although Excel may not show it in the function autocomplete or provide the same guidance as for newer functions. Type the function directly and check that the start date is not later than the end date. If the start date is later, DATEDIF returns the #NUM! error.

The function also supports other units. With the same date layout, “y” returns the number of complete years, while “ym” returns remaining complete months after complete years are excluded. For instance, a span of 2 complete years and 3 additional complete months can be described as 2 years and 3 months using those units. The “ym” result alone is only the remainder, not the total month count. Avoid treating every available DATEDIF unit as interchangeable: each answers a different question.

Count calendar-month boundaries instead

For a difference between the month and year labels on the two dates, calculate the year difference in months and add the difference between the month numbers. With the earlier or later dates in A2 and B2, enter:

=(YEAR(B2)-YEAR(A2))*12+MONTH(B2)-MONTH(A2)

This formula counts calendar-month boundaries, without checking the day of the month. From January 31, 2024, to February 1, 2024, it returns 1. From December 20, 2023, to January 5, 2024, it also returns 1: the year change is included in the calculation. From January 15 to March 14, it returns 2, whereas DATEDIF returns 1 because the second complete monthly period has not elapsed.

This approach is useful for grouping dates into monthly reporting periods or determining how many month labels separate two dates. It is not a substitute for a completed-month calculation in a contract or anniversary schedule. It also returns a negative number when B2 is in an earlier calendar month than A2, which may be useful for signed differences.

Handle reversed dates and blank cells

DATEDIF expects the start date first. If your data can be in either order and you want a signed number of completed months, with dates in A2 and B2, use:

=IF(B2>=A2,DATEDIF(A2,B2,"m"),-DATEDIF(B2,A2,"m"))

For example, if A2 is March 15 and B2 is January 15 of the same year, this returns -2. If the dates are equal, it returns 0. The formula compares the dates first, then passes the earlier one as the start date to DATEDIF.

Empty cells deserve attention in a working sheet. Excel stores dates as numbers, and a blank referenced in date arithmetic can behave like zero; that can produce a misleading result or an error. If either date might be missing, with the same A2 and B2 layout, use:

=IF(OR(A2="",B2=""),"",IF(B2>=A2,DATEDIF(A2,B2,"m"),-DATEDIF(B2,A2,"m")))

This leaves the result blank until both cells contain values. For a large worksheet, you can fill the formula down the results column using the fill handle. The formula returns a number, so apply a number format appropriate to your report rather than a date format.

Understand month ends and anniversaries

Month-end dates are where two reasonable interpretations often produce surprising results. DATEDIF compares complete months using the day positions as well as the month and year. With January 31 in A2 and February 28 in B2, =DATEDIF(A2,B2,"m") returns 0: the corresponding day number, 31, has not arrived in February. With January 31 and March 31, it returns 2.

That result may not match a business rule that treats the last day of one month as reaching the last day of the next month. A subscription might renew on the last calendar day, while a loan or age calculation may follow a separate anniversary rule. Decide the rule first, especially for dates on the 29th, 30th, or 31st, and check dates around February in both leap and non-leap years.

If your rule is based on an anniversary date, compare the formula’s result against a few known boundary cases before using it for many records. A useful test set includes a mid-month start, a start on the 31st, a February date, and an interval that ends one day before and one day after the expected monthly anniversary. This catches off-by-one-month results early.

Can the result be a decimal number of months?

There is no single exact decimal-month answer for two dates unless you define how long a month is. Calendar months have different numbers of days, so dividing elapsed days by 30, 30.4, or another chosen average gives an estimate, not the same result as counting completed calendar months. State the convention if the number will be used in a financial, contractual, or scientific context.

For an average-month estimate based on 365.2425 days per year, use the following with the earlier date in A2 and the later date in B2:

=(B2-A2)/30.436875

Excel stores valid dates as serial values, so subtracting the earlier date from the later one gives elapsed days. For January 1 to February 1, 2024, the difference is 31 days; dividing by 30.436875 gives approximately 1.019 months. That is an average-length estimate, not a claim that the interval is more than one calendar month for every business purpose. If you choose a 30-day convention instead, document it and use the corresponding divisor consistently.

Check the dates and format the result

A correct formula can still return an unexpected answer if one of its inputs is not a real Excel date. Dates entered as text may be interpreted differently depending on regional settings. A string such as 04/05/2024 can mean April 5 or May 4 in different date conventions. Use unambiguous date entry, confirm the cell displays as a date, or convert imported text to dates before relying on the result.

Check that both input cells contain dates and not labels or blank-looking text.
Confirm that the dates are in the intended order if using DATEDIF directly.
Use a number format for the answer. If a result looks like a date, change the result cell to General or Number.
Test dates on month ends and around year changes if those cases occur in your data.

Changing a cell’s appearance does not necessarily convert text into a genuine date value. Likewise, formatting a serial date as a number reveals its stored value but does not change the underlying date. For imported data, verify the conversion rather than assuming that a date-like display guarantees valid date arithmetic.

Choose the formula that matches the task

Use DATEDIF with “m” when the question is “How many complete months have elapsed?” Use the YEAR and MONTH calculation when the question is “How many calendar-month boundaries separate these dates?” Use a decimal estimate only when an average month length is acceptable and has been specified.

These choices matter in practical settings. For customer tenure measured by monthly anniversaries, a completed-month count is usually more meaningful than simply subtracting month numbers. For a chart that places records into calendar months, the boundary-count method may be the relevant comparison. For prorating, do not assume a generic decimal-month formula matches the organization’s policy; the policy may use actual days, a 30-day month, or another convention.

When the intended meaning is unclear, write it in the column heading or a note, such as “Completed months” or “Calendar months crossed.” That small clarification prevents readers from comparing a DATEDIF result with a month-boundary count as if they were the same measure.

Frequently Asked Questions

What is the formula for months between two dates in Excel?
For complete months, put the earlier date in A2 and the later date in B2, then use =DATEDIF(A2,B2,"m"). It does not count an incomplete final month.

Why does DATEDIF return one month less than I expect?
DATEDIF counts completed monthly periods and considers the day of the month. If the end date falls before the corresponding day in the final month, that month is not counted. A calendar-month-boundary formula will give a different result.

How do I calculate months between dates when the dates are reversed?
Use a signed formula that checks which date is earlier, such as the IF and DATEDIF formula above. This avoids passing the later date as DATEDIF’s start date and returns a negative count for a reversed interval.

Does DATEDIF count part of a month?
No. The “m” unit returns an integer number of complete months. For a decimal estimate, choose and disclose an average month length; for a calendar-specific period, define how month ends and anniversaries should work.

Why does my date-difference formula show an error or an odd number?
Check for a start date later than the end date in a direct DATEDIF formula, blank cells, or date values stored as text. Also check the result cell’s number format and test month-end cases, since those often explain a one-month difference.

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.