You can learn how to calculate months between two dates in Excel effortlessly if you follow the methods and steps outlined in this Microsoft Excel tutorial.

If you’ve ever struggled to calculate the time gap for financial reports, employee tenure, or billing cycles, you know how challenging this could be. Excel doesn’t have a simple Months Between function for such calculations. So you must use the right formulas to get accurate results. But nothing to worry about now! In this tutorial, I’ll present the proven methods and their steps with real examples and illustrations. By the end, you’ll confidently handle calculations related to months between two dates in Excel like a pro!

Using the DATEDIF Function

The simplest way to calculate months between two dates in Excel is by applying the DATEDIF function. It derives the number of years, months, and days between two dates. There are only three arguments and all of them are mandatory. However, it’s easy to formulate the DATEDIF formula for any dataset. Let me show you how below:

Let’s consider that your dataset looks similar to the one shown above, In this dataset, you start your data analysis task with a start and end date.

Create a column named Months in the adjacent column of the existing dataset. Select the first cell below the column header and enter the following formula into the cell:

=DATEDIF(B2,C2,"M")

In the above formula, B2, C2, and M are the start and end dates, respectively, and M is the unit in which Excel expresses the difference between them.

Hit Enter to calculate the elapsed months.

Now, use the fill handle of this cell and drag it down to calculate the rest of the cells in the Months column.

Excel should populate the months between the given dates.

Subtracting Dates and Converting to Months

You can also use the subtraction operator to get the total number of days between two dates and convert that value to months. Before you can proceed with this method, ensure that both the starting and ending dates are formatted in Short Date or Long Date format.

Now, go to your dataset where you wish to calculate the months between two dates, and select a cell where you’d like to get the result.

Enter the following formula into the selected cell:

=(C2-B2)/30

In this formula, C2 is the end date and B2 is the start date. You’re then dividing the days by 30 to get the months elapsed during these two dates.

Make sure you adjust these entries according to your own dataset.

Hit Enter to calculate the cell.

Drag the fill handle down the column to replicate the same formula in the rest of the cells.

You might get fractional values for months. To fix this, select the entire column or a range of cells, and click on the Decrease Decimal command in the Number block of the Home tab.

Using YEAR and MONTH Functions

You can use the YEAR and MONTH functions when you need to count a full month between two dates. Since the DATEDIF isn’t a documented function in Excel, it doesn’t show up as a syntax suggestion as well when you type the function initials. It helps you create a simple and readable formula with backward compatibility for most Excel desktop and online editions. This combination of the YEAR and MONTH formula calculates the difference in years, converts it to months, and adds the difference in months.

Let me show you below the formula syntax and the technique to use this method:

Firstly, select a cell where you want Excel to generate the output for the month value. There, enter the following formula:

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

You only need to change the cell range references in the above formula. For example, C2 should be the end date and B2 should be the start date. Keep the rest of the things as they are.

Hit Enter to calculate the cell.

You can now use the fill handle and drag it down to copy the same formula in all other cells of the column.

That’s it! You’ve successfully calculated months between two dates using YEAR and DAY functions.

Using INT and DAY Functions

When you use the INT and DAY functions, you count only the full months and correctly adjust incomplete months. The INT function rounds down the month difference, and the DAY function checks if the end date occurs before the start date’s day, subtracting one month if necessary. This method is ideal for billing cycles, employment tenure, and financial calculations, where accurate full-month counting is required.

When you’re ready to calculate the months between two dates in a separate cell, copy and paste the following formula into it:

=INT((C2-B2)/30) + IF(DAY(C2)>=DAY(B2),0,-1)

As usual, replace the cell range references in the above formula syntax using actual references according to your own dataset.

Hit Enter to calculate the months between the two dates.

You can use the fill handle to drag it down and get results for the rest of the rows if there are any.

Excel will now instantly generate a more targeted master plan e for its inventory.

Using Power Query

Sometimes, you might need to use Power Query to import large datasets from external servers or databases. In addition to cleaning, filtering, and transforming raw datasets, you can also perform calculations, such as calculating the months between two dates. 

This tool is preferable if your data updates frequently since Power Query can apply a preset data transformation and calculations. Another key benefit is query reusability—once set up, it can be applied to new datasets instantly. This method is useful for monthly reports, payroll, or project tracking.

The first step is importing your data into Power Query Editor. If you’re bringing in data from an external server, you can go to the Data tab and click on the Get Data command.

A context menu will open, showing different options for data connections, such as From Azure or From Database.

Hover the cursor over the preferred database source to bring up the overflow menu, which shows the final database connectors, like From Azure SQL Database. Click on an option and follow onscreen instructions to complete the data importing process.

For this tutorial, I have imported data from the active worksheet. To do that yourself, select the entire data table and click on the From Table/Range command in the Data tab.

Click OK on the Create Table dialog box to import your data to Power Query.

You should now see the Power Query Editor interface. Navigate to the Add Column tab and click on the Custom Column command.

The Custom Column dialog box will pop open.

Type a column header text in the New column name field, like Months Elapsed.

Copy and paste the following formula into the Custom column formula field:

=Duration.Days([Present Date]-[Buy Date])/30

The codes, like [Present Date] and [Buy Date] in the above formula syntax, represent the end dates and start dates of your dataset. So, replace these appropriately depending on the input dataset.

Click OK on the Custom Column dialog box to add the newly created column.

The Elapsed Months column will show up in the Power Query Editor.

If the months calculated are in fractions, select the column, click on the Rounding drop-down arrow in the From Number commands block, and select the Round Down or Round Up option. This will convert the fractional month values to their nearest whole month and create a new column named Round Down.

Click on the File tab and select the Close & Load To option from the context menu.

The Import Data dialog box will open on the active Excel worksheet.

Click on the Existing worksheet option and select a cell or cell range as the destination for the data you’re importing from Power Query.

Click OK to close the dialog box and complete exporting data to an Excel worksheet.

You can hide unnecessary columns to clear the clutter and make your datasets readable.

Congratulations! You’ve successfully calculated months between two dates using Power Query.

You can now use this query in the future to instantly generate month values from a new dataset.

Simply go to the Queries & Connections navigation panel on the right side of the Excel workbook.

The original Power Query Editor interface will open.

Double-click on the Source step in the Query Settings navigation panel on the right side.

If you initially created the query from a file or using an external server address, a dialog box will open allowing you to change the source dataset.

As soon as you link the new dataset, Power Query will automatically replace the existing data with the latest datasets.

Conclusions

Now you know how to calculate months between two dates in Excel using various Excel functions, like DATEDIF, the subtraction operator, YEAR, MONTH, INT, and DAY. Also, for a larger dataset, you’ve learned the steps to achieve the same result in Power Query.

If this tutorial helped you to learn a new Excel skill, share your acknowledgment in the comment box.