How To Calculate the Number of Months Between Two Dates in ExcelEasy to Follow Guide with Screenshots
If you are a manager, there are probably times when you are managing several projects at once that you would like to know the number of months between two dates, whether for planning a project, to know how many months before a project ends or for other purposes.
There are actually several ways to find this out, and they involve using different formulas.
So, to help you, we will show you some of the formulas you can use to find out the number of months between two dates and explain how to use them in this article.
Here are some formulas you can use to calculate the number of months between two dates in Excel.
The DATEDIF Function
You probably will not have dates that have an exact number of months.
Instead, you are probably going to get a certain number of months along with some days between the dates you are considering.
As an example, consider that there are three months and ten days between June 1 and September 10.
But, if you are only interested in finding the number of full months between your two dates, the DATEDIF function will work.
Look at the dataset below and assume that only the number of full months is needed, not days.
Here is the formula that you can use to compute the number of months between the two dates.
=DATEDIF(A2,B2,”M”)
This formula will tell you only the months between your given days.
It will not give you the months, along with any additional days short of a month between the dates.
If you want the months along with the days between your two dates, you can use the following formula.
=DATEDIF(A2,B2,”M”)&”M “&DATEDIF(A2,B2,”MD”)&”D”
We will use this formula with the same data set we used in the previous example.
As you can see, with this formula, we are given the months and days.
You might notice that the DATEDIF function does not include the starting date when it counts the number of months.
For instance, suppose a project started on June 1 and was completed on June 30.
The DATEDIF function would count this as zero months because it does not include the start date.
Therefore, as far as it is concerned, only 29 days in June were covered, so it is not a month.
The YEARFRAC Function
This is another method you can use to obtain the number of months between two dates.
It is called the YEARFRAC Function. This function will use the start date and end date you provide as input arguments.
Then it will provide you with the number of years between the two dates.
The values given by the YEARFRAC Function will be in decimal form if the period between the two dates is less than a year.
As an example, suppose you have a starting date of 04 February 2021 and an end date of 2 January 2022.
The result you would obtain by using the YEARFRAC Function would be .911111. But all you would need to do to obtain the value in months would be to multiply this result by twelve.
We will use the same dataset that we used with the previous methods.
We will use the YEARFRAC Function to find the number of months between each of the start and end dates.
Here is the formula we will use.
=YEARFRAC(A2,B2)*12
Using this formula will give you the results in decimals.
However, if you want the number of complete months in these time periods, you could use the formula below.
=INT(YEARFRAC(A2,B2)*12)
One important difference between the DATEDIF function and the YEARFRAC function is the fact that the YEARFRAC function does count the start date as part of the month when counting months.
So, if your start date is September 1 and your end date is September 30, the formula will count this as one month.
The Year and Month Formula
This formula is useful for people who want to know how many months there are between two dates.
We will use our dataset from above again.
Here is the formula you can use to calculate the months in these periods of time.
=(YEAR(B2)-YEAR(A2))*12+MONTH(B2)-MONTH(A2)
The formula above makes use of the YEAR function.
So, it provides you with the year number by using the date and the MONTH function, which gives you the month by using the date.
The formula will disregard the month containing the start date.
Therefore, if your start date was 14 October and your end date was 25 November, it would count as one month.
If you would prefer the month containing the start date to be counted, you can use the following formula.
=(YEAR(B2)-YEAR(A2))*12+(MONTH(B2)-MONTH(A2)+1)
This formula is useful for those who want to know how many months a project has been taking place rather than how many full months a project has been going on for or how many full months it took for a project to be completed.
However, this does mean that a month will be counted even when the project was only in effect for a few days.
Conclusion
In this article, we have shown you three ways to count the number of months between two dates in excel.
These include the following:
DATEDIF Function
This is useful for obtaining the number of full months between two dates without counting the start date.
YEARFRAC Method
This method gives the value of the months between two dates, and the result will generally be a decimal value.
The integer will tell you the number of full months, and the decimal part of the number will tell you the number of days.
The Year and Month Method
This method is useful if you want to know how many months a project has been active, even if the project only covers a very short period of time.
These methods are all very useful for calculating the number of months between two dates in Excel.
But, it is important to consider exactly which days or months you want the count to include in order to get an accurate count for your purposes.