Calculate Months Between two Dates in Excel

There are a few ways to calculate the number of months between two dates. I’ll show you all the methods that are available in Excel.

The DATEDIF function

You can count months between two dates with the DATEDIF function. This function exists for the same reason why you can start typing a formula with (+) instead of (=) – for compatibility with Lotus 1-2-3.

The DATEDIF function is only documented in Excel 2000. If you start typing it inside Excel, you won’t get any suggestions.

If you want to use this function, you have to remember its syntax.

=DATEDIF(start_date,end_date,”d”)	Days
=DATEDIF(start_date,end_date,”m”)	Months
=DATEDIF(start_date,end_date,”y”)	Years

The first argument has to be the earlier date, the second is the later date and the third one indicates, whether we want to count days, months, or years.

This is how it looks in our example.

Here, there are three examples.

The first one returns 24 months.

The second one returns 26, months, without showing additional days.

The third example uses the following formula.

The first argument is the later date, and the second one is the earlier date. If you execute this function, it will return the #NUM! error.

Simple arithmetic operation

If you treat a year as twelve 30-day months, you can use the following formula.

Each day equals one, so subtracting erlier_date from end_date gives us the number of days. Then we divide it by 30, and it returns the number of months.

You can also use the ROUNDDOWN function if you don’t want to have the decimal part.

YEAR and MONTH functions

Another way, you can calculate months is by using this formula:

Let’s see how this formula works. I’m going to split it into two parts.

This formula takes the year part from the date and multiplies it by the number of months.

The result is 2 * 12 = 24.

The next part takes a month from the date.

This equals 4. 24 + 4 = 28 months.

The YEARFRAC function

The YEARFRAC function returns the year fraction.

We want a month, not years, so we are going to calculate the result by 12 to get the number of months.

Use the ROUNDDOWN function to get rid of the decimal fraction.