How to Calculate Months Between Two Dates in Excel

This tutorial will help you learn how to calculate the number of months between two dates in Excel. Our helpers will be the DATEDIF function, the YEARFRAC and INT functions, and the YEAR and MONTH functions. Find out how to:

  • get text added to the results,
  • only get complete months in the results,
  • round off the results to the nearest whole month,
  • get the months as fractions,
  • find the active months between two dates.

That and more but how does this all help? You have start dates and end dates of projects or campaigns and need to calculate the gap between the dates. You could be calculating duration, deadlines, or even ages and that is where this guide will come in handy. Time to move on to figure which method suits you best.

Let's get calculating!

How To Calculate Months Between Two Dates In Excel

Using DATEDIF Function

The DATEDIF function is used to calculate the number of days, months, or years between two dates. DATEDIF is an undocumented Excel function and won't show up in Formula AutoComplete. Since there will be no preview of its syntax, you need to know the syntax of the function:

Syntax:

=DATEDIF(start date,end date,unit)

start date – the starting date of the period to be measured.
end date – the end date of the period to be measured.
unit – type of duration to be returned e.g. "M" for the complete number of months, "Y" for complete years.

Return Complete Months

Using the syntax above, this is the formula we will use:

=DATEDIF(C3,D3,"M")

The DATEDIF function takes cells C3 as the start date and D3 as the end date, measures the duration between them, and returns the result in the provided unit. We are using the DATEDIF function to calculate the months taken to write a collection of books and have supplied the formula with "M" as the unit.

DATEDIF has measured the time to be 3 complete months between 1/17/2025 and 4/25/2025 and returns "3" as the result of the formula.

Return Complete Months

Return Nearest Whole Month

Now take a look at cell E8. The DATEDIF function has returned 1 month as the duration between the dates 8/2/2025 and 10/1/2025. Although evidently, the duration is closer to 2 months, DATEDIF will only return the number of complete months as it rounds the result down to the nearest month.

If you'd rather the DATEDIF function returns the nearest whole month, you can adjust the formula used above with the addition of 15 days to the end date. This adjusts the previous formula to the following:

=DATEDIF(C3,D3+15,"M")

Adding 15 days to the end date stretches the duration over so that the function rounds the result up to the nearest whole month instead of rounding it down. Taking our example of cell E8, the end date was one day short of falling into 2 months, and the plain DATEDIF function calculated the duration as 1 month. By adding 15 days to the last date, we can make the duration extend enough to be included in the result as the second month.

Here's how the 15-day adjustment turns out against DATEDIF applied simply:

Return Nearest Whole Month

Return Months and Days

Or let's not leave anything to conjecture and get the DATEDIF function to return the number of months and days. The formula is a bit longer but nothing complicated. Here's the formula using the DATEDIF function to return the months elapsed between two dates in months and days:

=DATEDIF(C3,D3,"M")&"m "&DATEDIF(C3,D3,"MD")&"d"

The first part of the formula is an exact copy of the formula used to return complete months i.e. DATEDIF(C3,D3,"M"), the result being 3 months. The second part of the formula uses the "MD" expression to return the number of days separate from complete months between the two dates. The number of days calculated between C3 and D3 is 8.

Then the formulas have been coined together using the ampersand "&". The ampersand is used to join the formulas and bits of text to be added after the months and days. After the first DATEDIF formula the added text is "m " after the complete months and "d" after the number of days. This brings us to 3m 8d as the outcome.

Return Months and Days

Using YEARFRAC & INT Functions

YEARFRAC function returns the difference between two dates in terms of years. The results of the YEARFRAC function can also be expressed as a fraction and this is where the INT function comes into play.  INT function rounds the results down to the nearest integer.

YEARFRAC Function

The YEARFRAC function takes two dates and returns the fraction of year, the days between the two dates represent. This is the formula with the YEARFRAC function to return the number of days between two dates as a fraction along with complete months:

=YEARFRAC(C3,D3)*12

The YEARFRAC function has the start date and end date to calculate the number of days as a fraction and it returns 0.3 in the first instance of our example. That result has been multiplied by 12 in the formula since we want the number of months. Now the formula returns 3.3 months.

YEARFRAC Function

YEARFRAC & INT Functions

For the complete number of months without fractions, you only need to nest the formula above in the INT function. The INT function rounds numbers down to the nearest integer. Below is the formula of the YEARFRAC function nested in the INT function to return whole months:

=INT(YEARFRAC(C3,D3)*12)

The YEARFRAC function is used in the same way as seen previously; with the start date and end date and the fraction multiplied by 12, returning 3.3 months. The INT function rounds this down to the nearest integer i.e. 3 and returns that as the result.

YEARFRAC and INT Functions

Note that you can add 15 days to the end date (D3+15) in this formula to return the results rounded to the nearest whole month:

YEARFRAC & INT Functions

Using YEAR & MONTH Functions

The YEAR function extracts the year from a date. The MONTH function extracts the month from a date and returns the month number. For our case example, we will use the YEAR and MONTH functions and subtract the dates from each other to get the number of months between them. The following is the formula to get the number of months between two dates with the YEAR and MONTH functions:

=(YEAR(D3)-YEAR(C3))*12+MONTH(D3)-MONTH(C3)

Since both the functions take single arguments, we have used each function twice to subtract the earlier date from the later date to get the number of months.

The year of the start date subtracted from the end date and the month of start date subtracted from the month of the end date gives us the duration in between the dates. The year part has been multiplied by 12 for the calculations to be based on months. Both sides, the year and month, have a combined result with the plus sign in between.

Using YEAR & MONTH Functions

Let's take an instance from the example which is dealing in two different years. Subtracting the year of cell C9 from D9, (2026-2025) gives us 1 year. Multiplied by 12, we have 12 months. As for the months, 11 will be deducted from 1, giving us -10. The formula condenses down to 12+(-10) and we arrive at 2 months as the result.

You might be thinking that things would be so much simpler had we only used the MONTH function like so:

=MONTH(D3)-MONTH(C3)

to get the difference in months. That's quite a good idea and you can go ahead with it if you're dealing with the same year in both dates. But if you have different years, as we do, it's much easier to just apply one formula including the YEAR and MONTH functions.

Active Months Between the Dates

Now cast a look over row 12. The book had been started earlier in June, ending later in July, having taken 1 month 20 days but with our formula, we have 1 month as the resulting duration. If you want to find the active months of the dates, (e.g. you want to find how many months the book was being written in), use this formula:

=(YEAR(D3)-YEAR(C3))*12+(MONTH(D3)-MONTH(C3)+1)

The formula is the same as the one used above with the addition of the number 1 to the month of the start date. In the first instance, we were previously getting the duration as 3 months. Now we're getting 4 months as the active months for the book writing are January, February, March, and April.

Active Months between the Dates

That would be all of our easy peasy ways of calculating the number of months between two dates in Excel. Hope you found the little tricks here and there useful for your calculations. You'll have us back with more calculations to tread the Excel path skillfully!

Compiled by - Content Studio

Thanks for reading. If you have found this article helpful show your love by sharing it with your friends & colleagues. All the tutorials on the Excel Trick are produced, reviewed, and fact-checked by a team of experts. You can check out our team here.