Number of Months Between Two Dates in Excel: 5 Easy Ways to Find

Microsoft Excel is one of the greatest tools which allows you to find the number of months between any two dates by providing a number of unique functions. Through these functions, you’ll be able to find out the time span of any incident or someone’s age at once by inserting two fixed dates only.


Download Practice Workbook

You can download our Excel workbook which we’ve used to prepare this article. You can also use this practice book as a calculator by inputting dates in the specific fields to work out the number of months between two dates as instructed.


5 Easy Methods to Find Number of Months Between Two Dates

1. Using Excel DATEDIF Function to Find Number of Months

Let’s think of some random projects with their launch dates & closing dates in a spreadsheet. We have to find the number of months as the time span of all projects. We’ll use the DATEDIF function here. The DATEDIF function returns the difference of days, months, or years between two dates, based on three arguments, one starting end, one ending date, and one argument called unit.

Excel DATEDIF Function to Find Number of Months

Step 1:

⇒ Select Cell E4 where we have to find the time span as months.

⇒ Type =DATEDIF(C4,D4,"M") & press Enter.

You’ll get the number of months as a time span for the first project in Cell E4.

Here, inside the formula syntax,

C4 is the Launch Date

D4 is the Closing Date

“M” is the parameter for the number of months to be counted in this function.

Excel DATEDIF Function to Find Number of Months

Step 2:

⇒ Now use the Autofill or Fill Handle option to determine the time span in months for the rest of the projects.

If you don’t know how to use the Autofill or Fill Handle option, then here you go.

You have to point your mouse cursor onto the right bottom corner of Cell E4, you’ll find a ‘+’ icon, click on it, drag it to the Cell E8, release the mouse button & then you’re done.

If we use ‘Y'(Years) or ‘D'(Days) in lieu of ‘M'(Months) in the parameter part inside the function bar, we’ll get the differences between two dates as a number of years or days too.

Note: This function will not calculate the running month, but will stop at the previous completed month.


2. Inserting YEARFRAC along with INT or ROUNDUP Function(s) to Ignore Decimals

Now we’re going to use the YEARFRAC function to work with the previous dataset. The YEARFRAC function returns the year fraction representing the number of whole days between start_date and end_date.

Step 1:

⇒ Select Cell E4

⇒ Type =INT(YEARFRAC(C4,D4)*12) & press Enter.

You’ll see the same result as found before.

YEARFRAC along with INT or ROUNDUP Function(s) to Ignore Decimals

Here, we’re finding out the number of years as time span first which will be shown in decimal format. Then this value will be multiplied by 12 (No. of months in a year). We’ll use the INT function at the initial to convert decimal into integer format.

Step 2:

⇒ Now use Fill Handle again like before to find out the number of months as time span for the rest of the projects.

YEARFRAC along with INT or ROUNDUP Function(s) to Ignore Decimals

We can use the ROUNDUP function instead of INT too at the initial. But there’s a difference between these 2 functions.

The INT function won’t round off the decimal value so it’ll omit the decimal parts even if it’s too close to the next integer value.

But the ROUNDUP function will allow you to round off the number up to a fixed decimal place or nearest whole number according to your choice.


3. Combining YEAR & MONTH Functions to Find Number of Months Between Two Dates in Excel

Here’s another method you can also use to get the same results. And we’ll combine YEAR and MONTH functions in this method. The YEAR function returns the year of a date, an integer in the range 1900-9999. And the MONTH function returns the month, a number from 1 (January) to 12 (December).

Steps:

⇒ In Cell E4, type =(YEAR(D4)-YEAR(C4))*12+MONTH(D4)-MONTH(C4)

⇒ Now Press Enter & Autofill the whole column like before.

What we’re doing here in Cell E4 is-

i) Finding the differences between years,

ii) Converting years into months,

iii) Adding differences between the ranks or orders of two months.

YEAR & MONTH Functions to Find Number of Months Between Two Dates in Excel


4. Determining Number of Years, Months & Days Together Between Two Dates in Excel

Now you’ll be able to learn how you can find out the number of years, months & days together between two dates.

Steps:

⇒ In Cell E4, type =DATEDIF(C4,D4,"Y")&" Year(s) "&DATEDIF(C4,D4,"YM")&" Month(s) "& DATEDIF(C4,D4,"MD")&" Day(s)"

⇒ And hit the Enter button.

Autofill the whole column by using Fill Handle & you’re finished.

So, here we’re using DATEDIF functions again but this time we’ll add some text functions like “Year(s)” too by using Ampersand(&) which will create spaces between words or number(s).

Before 3 units of time, we’re using the DATEDIF function every time to find out the number of years, months & days separately.

Determining Number of Years, Months & Days Together Between Dates in Excel


5. Using Subtraction Formula with MONTH Function in Excel

In this last part of all methods, we’ll now combine MONTH functions & simple subtraction formula.

What you have to do here is subtract an old date from a new date by using MONTH functions for both dates & you’re done.

Subtraction Formula with MONTH Function in Excel

But this method has a drawback. It’ll allow you to find the number of differences between two months only within a fixed year.


Conclusion

I hope, this article has guided you through the proper instructions thoroughly as I’ve tried to illustrate each of the methods in a convenient way as possible. If you got any question(s) or feedback regarding this article you can comment. You can also go through other useful articles related to basic & advanced Excel functions on our website.


Further Readings

Nehad Ulfat

Hello, Welcome to my profile. I'm a Technical Content Creator as well as a Naval Architect & Marine Engineer. I have preferences to do analytical reasoning & writing articles on various statistical data. Here, you'll find my published articles on Microsoft Excel & other topics related to my interests!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo