Future-Proof Your Career, Master Data Skills + AI

Blog

Blog

# How to Calculate Months Between Two Dates in Excel: Top 3 Ways

by | 4:06 pm EST | January 28, 2024 | Excel

Calculating the number of months between two dates in Excel is an essential Excel skill. The best part? It is easy to do.

To calculate the number of months between two dates in Excel, you can use a combination of the YEAR, MONTH, and DATEDIF functions.

In this article, you’ll learn how to use these functions to get the exact count of months between two dates.

By the end, you’ll be well-equipped to confidently perform month calculations in Excel, regardless of the complexity or number of dates involved.

Let’s get into it!

Table of Contents

## 1. Using the DATEDIF function to Calculate Months in Excel

The DATEDIF function (Date Different Function) is not as well-known as some of the other date and time functions in Excel. This is partly due to the fact that it is not directly listed in the available functions in the Function Library.

However, the DATEDIF function is a very useful function for working with dates in Excel, and it is especially useful for calculating the difference between two dates in months.

The syntax for the DATEDIF function is as follows:

=DATEDIF(start_date, end_date, “interval”)

• start_date is the starting date.

• end_date is the ending date.

• interval is the time unit to use for the calculation (i.e., “Y” for years, “M” for months, or “D” for days).

To calculate the number of months between two dates in Excel, you can use the DATEDIF function with the “M” interval.

Suppose you have two dates in cells A2 and B2 and want to find the number of months between these two dates. The following formula will give you the desired result:

=DATEDIF(A2, B2, “M”)

To use the DATEDIF function in Excel, follow these steps:

1. Click on the cell where you want the result to be displayed.

2. Type the equal sign (=) followed by the DATEDIF function name and open parenthesis (

=DATEDIF(

3. Enter the start_date, end_date, and interval arguments separated by commas and enclosed in double quotation marks.

4. Press Enter to complete the calculation.

Now you have the number of months between two dates. The result will be a whole number, which represents the number of complete months between the two dates.

Note that the DATEDIF function does not count the day portion of the date, only the number of completed months.

So, if you want to include the day portion in the calculation, you may need to use other formulas.

By understanding the syntax and how to use the DATEDIF formula, you can easily perform these calculations and enhance your data analysis in Excel.

## 2. Calculating Months with the YEAR and MONTH Functions

The YEAR and MONTH functions in Excel are used to extract the year and month from a given date, respectively.

These functions can be combined with other functions to calculate the number of months between two dates.

To calculate the number of months between two dates in Excel, follow these steps:

Step 1

Enter the start date in cell A2 and the end date in cell B2.

Step 2

In cell C2, use the below formula.

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

The formula above uses the YEAR function and MONTH function to extract the year and month from the start and end dates, and then calculates the number of months between them.

It’s important to note that the formula does not consider the day of the month when calculating the number of months.

For example, if the start date is “Jan 15, 2025” and the end date is “Mar 10, 2025”, the formula will return 2 months, even though it’s only 1 month and 25 days between the two dates.

If you want to find the complete number of months, you can use the DATEDIF function.

### 3. TEXT Function

The TEXT function is a versatile function in Excel that can be used to format dates in various ways. You can also use it to calculate the difference in days and months between two dates.

To calculate the difference in months, use the following formula:

=TEXT(end_date – start_date, “m”)

For example, if the start date is in cell A2 and the end date is in cell B2, the formula would be:

=TEXT(B2 – A2, “m”)

This will return the difference in months between the two dates.

In the above example, both the start date and end date are in the same year. But, if the years of the start date and the end date are not in the same year, you’ll get inaccurate results.

## How to Calculate the Difference in Days and Months

In this section, we will cover how to use the DATEDIF function to calculate the difference in days and months between two dates in Excel.

### DATEDIF Function

The DATEDIF function is a built-in function in Excel that calculates the difference between two dates. Its syntax is:

=DATEDIF(start_date, end_date, unit)

• start_date: The starting date.

• end_date: The ending date.

• unit: The time unit to use for the calculation. Use “d” for days, “m” for months, or “y” for years.

To calculate the difference in months and days, use the following formula:

=DATEDIF(start_date,end_date,”M”)&” Months & “&DATEDIF(start_date,end_date,”md”)&” Days”

For example, if the start date is in cell A1 and the end date is in cell B1, the formula would be:

=DATEDIF(A2,B2,”M”)&” Months & “&DATEDIF(A2,B2,”md”)&” Days”

In the above formula, the first part (DATEDIF(A2,B2,”M”)&” Months & ) calculates how many months are full months and the latter part (DATEDIF(A2,B2,”md”)&” Days”) counting the balance number of days.

## Final Thoughts

In this article, we’ve explored how to calculate the number of months between two dates in Excel.

We’ve also discussed various functions like the DATEDIF, YEAR, MONTH, and TEXT functions. These functions allow you to accurately find the difference in months between two dates.

Also, you’ve learned about the limitations and potential issues that can arise when working with date calculations in Excel.

By understanding these limitations and using the appropriate functions, you can confidently perform month calculations in your spreadsheets.

Want to learn how to clean your data like an expert? Check out the video below!

## Frequently Asked Questions

### How can I calculate the number of months between two dates using a formula in Excel?

To calculate the number of months between two dates, you can use the DATEDIF function with the “m” unit.

Assuming the start date is in cell A2 and the end date is in cell B2, the formula would be =DATEDIF(A2, B2, “m”).

### What is the best way to calculate the number of months between two dates using a formula in Google Sheets?

In Google Sheets, you can use the DATEDIF function as well, but it’s an undocumented function.

The formula would be =DATEDIF(A2, B2, “m”).

### How do I calculate the difference between two dates in years and months in Excel?

To calculate the difference between two dates in years and months, you can use the DATEDIF function with the “ym” unit.

The formula would be =DATEDIF(A2, B2, “ym”).

### Is there a way to calculate the number of months between two dates excluding weekends in Excel?

You can calculate the number of working days between two dates using the NETWORKDAYS function and then convert the result to months.

The formula would be =NETWORKDAYS(A2, B2) / 21.75.

## How to Calculate Percentile in Excel: 4 Great Methods

Are you looking to calculate a percentile in Excel? Well, you're in the right place. To calculate a...

## How to Find Relative Frequency in Excel: 4 Quick Methods

Calculating relative frequency is a fundamental concept in statistics, providing valuable insights into...

## How to Interpolate in Excel: User Guide With Examples

In data analysis, interpolation plays a crucial role in estimating values that fall between known data...

## How to a Take Screenshot in Excel: A Guide for Windows & Mac

As a data analyst or a spreadsheet enthusiast, you’re probably familiar with the many ways Excel can...

## How to Add Numbers in Excel: 3 Top Methods Explained

One of the fundamental operations you perform in Excel is adding numbers. Whether you’re managing...

## Excel Showing Formula Instead of Result? 5 Quick Fixes

Have you ever found yourself in a situation where instead of showing the results of your calculations,...

## How to Hard Code in Excel: 5 Easy Methods Explained

Have you ever wanted to input a value into a cell in Excel and make sure that it remains unchanged, no...

## How to Copy a Formula Down in Excel: A Quick Guide

Copying formulas down in Excel is a handy skill that can save you a lot of time and effort! Whether...

## How to Remove Time from Date in Excel: 4 Quick Ways

When you’re dealing with dates and times in Excel, you might find it necessary to separate the time...

## How to Sum a Column in Excel: 5 Ways (Shortcut Included)

One of the best things about Excel is how quickly and easily it can add up all the numbers in a column....

## How to Calculate Skewness in Excel: Formulas Explained

Do you want to calculate the skewness of your data to understand its distribution? Well, you're in the...

## How to Remove Apostrophe in Excel: 4 Quick Ways

Apostrophes have their place in Excel; however, when not needed, they can clutter your data and make it...