Excel: How Many Complete Months Are Between Two Dates?

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. For example, January 15 to March 14 is one complete month; January 15 to March 15 is two.

What “months between dates” can mean

There is not one universal answer to “how many months?” because a month is not a fixed number of days. Calendar months have 28 to 31 days, and two dates can be compared in at least two sensible ways:

Complete months: Count whole monthly intervals that have elapsed from the starting date. This is usually the right choice for age, tenure, subscription duration, or elapsed time. Excel’s DATEDIF function with the unit "m" returns this result.
Calendar-month difference: Count how many month boundaries separate the dates, without considering the day of the month. This is useful for grouping dates by month or measuring a change in calendar periods.

For instance, January 31 to February 1 crosses into a new calendar month, so the calendar-month difference is one. It is not a complete month, however. Decide which interpretation fits your task before choosing a formula; the answers can differ even when both are calculated correctly.

Calculate complete months with DATEDIF

With the start date in A2 and the end date in B2, enter this formula in C2:

=DATEDIF(A2,B2,"m")

The "m" unit asks for the number of complete months between the dates. If A2 contains January 15, 2024, and B2 contains March 14, 2024, the result is 1. The second monthly anniversary, March 15, has not yet arrived. Change B2 to March 15 and the result becomes 2.

DATEDIF is an older compatibility function supported in current Excel, including Microsoft 365 for Windows. It may not appear in Excel’s function autocomplete or formula suggestions, but you can type it directly. The function is useful here because it handles the varying lengths of calendar months rather than dividing a day count by an assumed number of days.

Dates need to be in chronological order for this formula. If the start date is later than the end date, DATEDIF returns #NUM!. A cell containing text that looks like a date may also cause problems, since Excel needs date values rather than merely date-shaped text.

Enter the formula and fill it down

For a list of date pairs, put each start date in column A and its matching end date in column B. Use this sequence to calculate the result for the first row:

Enter real Excel dates in A2 and B2. For example, type 1/15/2024 and 3/14/2024, then confirm Excel recognizes them as dates.
Select C2 and enter =DATEDIF(A2,B2,"m").
Press Enter. C2 should display 1 for those example dates.
To calculate more rows, select C2 and drag its fill handle down, or copy and paste the formula into the remaining cells. The relative references adjust for each row.

If the result looks like a date rather than a number, select the result cells and choose Home > Number Format > General or Number. The result is a count, not a date serial that should be displayed as a calendar date. On Windows, Ctrl+C and Ctrl+V copy and paste; on Mac, use Command+C and Command+V. The formula itself works in Excel for Mac and Excel for the web as well, though ribbon placement and some interface details can vary.

Count calendar-month boundaries instead

If you want the difference between the dates’ year-and-month labels, use this formula. With the earlier or later date in A2 and the other date in B2, enter it in C2:

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

For January 31, 2024 in A2 and February 1, 2024 in B2, the calculation is (2024−2024) × 12 + 2 − 1, which returns 1. This method ignores the day number entirely. Reversing the dates returns a negative result, which can be useful when you want a signed difference. It counts month transitions, not the number of full months experienced.

Another example is December 20, 2023 to January 5, 2024. The formula returns 1 because the dates fall in adjacent calendar months, even though only 16 days separate them. For reporting by month, this can be exactly the intended measure. For a contract that requires one full month of service, it usually is not.

Handle reversed dates when you need a signed result

If users may enter dates in either order and you want a signed count of complete months, place the two dates in A2 and B2 and use this formula in C2:

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

The formula switches the arguments so that DATEDIF always receives the earlier date first, then applies a minus sign when B2 is earlier. For March 15, 2024 in A2 and January 14, 2024 in B2, it returns -2: two complete months separate the dates, with the second date before the first. This formula assumes both cells contain valid dates. If you would rather flag reversed entries than show a negative value, use the basic DATEDIF formula and correct the input order.

Month-end dates and anniversary rules

Month-end dates are a common source of confusion. With January 31 in A2 and February 28 in B2, =DATEDIF(A2,B2,"m") returns 0 because a complete monthly interval has not elapsed under the function’s day-based comparison. The calendar-month formula returns 1 because February is a different calendar month from January.

Some policies treat the last day of one month to the last day of the next as a full month—for example, January 31 to February 28 in a non-leap year. Others require the same numbered day to arrive, or use a specified billing date. Excel cannot determine that business rule for you. Agree on how to treat dates such as January 30 or 31, leap-year February, and a date on the 29th, 30th, or 31st before building a report or payment schedule.

When the rule is based on a specific monthly anniversary, state it explicitly rather than assuming that every system counts month-end dates the same way. For financial, legal, or billing calculations, verify the formula against the governing policy and a few boundary examples. A one-day difference can change whether a complete period has elapsed.

Common errors and checks

#NUM!: With the basic formula, the start date is later than the end date. Swap the arguments or use the signed formula above.
#VALUE! or an unexpected result: One or both cells may contain text rather than Excel date values. Try selecting the cells and applying a date format; if the displayed value does not change, check how the data was imported or entered.
A result of zero: This can be correct. It means fewer than one complete month elapsed under the DATEDIF rule, not that the dates are identical.
A result formatted as a date: Change the output cell’s number format to General or Number.
Unexpected results from typed dates: Dates such as 3/4/2024 can mean March 4 or April 3 depending on regional settings. Use unambiguous dates when entering or importing data, and confirm the date Excel displays.

For a quick check, compare the result with the dates’ monthly anniversaries and test boundary cases around the day of the month. If a formula is copied down, also verify that each row’s start date is paired with the intended end date; a correct formula cannot correct mismatched records.

Frequently Asked Questions

What formula counts the number of months between two dates in Excel?
Use =DATEDIF(A2,B2,"m") for complete months, with the earlier date in A2 and the later date in B2. It returns a whole-number count and does not count a partial month as a full one.

Does DATEDIF count partial months?
No. With the "m" unit, it returns completed months only. To count month boundaries instead, use the YEAR-and-MONTH formula above; that formula does not measure partial-month duration either, but it counts calendar transitions.

How do I calculate months and remaining days?
First calculate completed months with DATEDIF, then calculate leftover days using the "md" unit. With the start date in A2 and end date in B2, the two formulas are =DATEDIF(A2,B2,"m") and =DATEDIF(A2,B2,"md"). The second reports remaining days after whole months, but the "md" unit has known limitations for some date combinations, so test month-end cases before relying on it for important calculations.

Can Excel return a negative number of months?
The basic DATEDIF formula cannot take a later start date and return a negative result; it produces #NUM!. Use the signed IF formula in this guide if dates can appear in either order.

Why does January 31 to February 28 show zero months?
DATEDIF counts completed intervals based on the dates, while a calendar-month formula counts a change from January to February. If your organization defines the last day of each month as a monthly anniversary, apply that rule explicitly rather than relying on either count without checking it.

Which method should you choose?

Use DATEDIF for completed months between two dates, the month-boundary formula for changes in calendar month, and a signed version when dates may be reversed. The key is to define what “a month” means for the question being answered. Once that rule is clear, choose representative dates—including a month-end pair—and verify the outputs before filling a formula through a large worksheet.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.