How do I calculate the difference between two dates in Excel without Datedif?

If you manage multiple projects, you would have a need to know how many months have passed between two dates. Or, if you’re in the planning phase, you may need to know the same for the start and end date of a project.

There are multiple ways to calculate the number of months between two dates (all using different formulas).

In this tutorial, I will give you some formulas that you can use to get the number of months between two dates.

So let’s get started!

This Tutorial Covers:

• Using DATEDIF Function (Get Number of Completed Months Between Two Dates)
• Using YEARFRAC Function (Get Total Months Between Two Dates)
• Using the YEAR and MONTH Formula (Count All Months when the Project was Active)

Using DATEDIF Function (Get Number of Completed Months Between Two Dates)

It’s unlikely that you will get the dates that have a perfect number of months. It’s more likely to be some number of months and some days that are covered by the two dates.

For example, between 1 Jan 2020 and 15 March 2020, there are 2 months and 15 days.

If you only want to calculate the total number of months between two dates, you can use the DATEDIF function.

Suppose you have a dataset as shown below where you only want to get the total number of months (and not the days).

Below is the DATEDIF formula that will do that:

=DATEDIF(A2,B2,"M")

The above formula will give you only the total number of completed months between two dates.

DATEDIF is one of the few undocumented functions in Excel. When you type the =DATEDIF in a cell in Excel, you would not see any IntelliSense or any guidance on what arguments it can take. So, if you’re using DATEDIF in Excel, you need to know the syntax.

In case you want to get the total number of months as well as days between two dates, you can use the below formula:

=DATEDIF(A2,B2,"M")&"M "&DATEDIF(A2,B2,"MD")&"D"

Note: DATEDIF function will exclude the start date when counting the month numbers. For example, if you start a project on 01 Jan and it ends on 31 Jan, the DATEDIF function will give the number of months as 0 (as it doesn’t count the start date and according to it only 30 days in January have been covered)

Using YEARFRAC Function (Get Total Months Between Two Dates)

Another method to get the number of months between two specified dates is by using the YEARFRAC function.

The YEARFRAC function will take a start date and end date as input arguments and it will give you the number of years that have passed during these two dates.

Unlike the DATEDIF function, the YEARFRAC function will give you the values in decimal in case a year has not elapsed between the two dates.

For example, if my start date is 01 Jan 2020 and end date is 31 Jan 2o20, the result of the YEARFRAC function will be 0.833. Once you have the year value, you can get the month value by multiplying this with 12.

Suppose you have the dataset as shown below and you want to get the number of months between the start and end date.

Below is the formula that will do this:

=YEARFRAC(A2,B2)*12

This will give you the months in decimals.

In case you only want to get the number of complete months, you can wrap the above formula in INT (as shown below):

=INT(YEARFRAC(A2,B2)*12)

Another major difference between the DATEDIF function and YEARFRAC function is that the YEARFRAC function will consider the start date as a part of the month. For example, if the start date is 01 Jan and end date is 31 Jan, the result from the above formula would be 1

Below is a comparison of the results you get from DATEDIF and YEARFRAC.

Using the YEAR and MONTH Formula (Count All Months when the Project was Active)

If you want to know the total months that are covered between the start and end date, then you can use this method.

Suppose you have the dataset as shown below:

Below is the formula that will give you the number of months between the two dates:

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

This formula uses the YEAR function (which gives you the year number using the date) and the MONTH function (which gives you the month number using the date).

The above formula also completely ignores the month of the start date.

For example, if your project starts on 01 Jan and ends on 20 Feb, the formula shown below will give you the result as 1, as it completely ignores the start date month.

In case you want it to count the month of the start date as well, you can use the below formula:

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

You may want to use the above formula when you want to know-how in how many months was this project active (which means that it could count the month even if the project was active for only 2 days in the month).

So these are three different ways to calculate months between two dates in Excel. The method you choose would be based on what you intend to calculate (below is a quick summary):

• Use the DATEDIF function method if you want to get the total number of completed months in between two dates (it ignores the start date)
• Use the YEARFRAC method when you want to get the actual value of months elapsed between tow dates. It also gives the result in decimal (where the integer value represents the number of full months and decimal part represents the number of days)
• Use the YEAR and MONTH method when you want to know how many months are covered in between two dates (even when the duration between the start and the end date is only a few days)

Below is how each formula covered in this tutorial will count the number of months between two dates:

What can I use instead of Datedif in Excel?

How to calculate the number of months between two dates in Excel without Datedif?

What is the formula for different date calculations?

What is the formula for calculating difference in Excel?