How to Add Months to a Date in Excel

When working with dates in Excel, you may need to add months to a particular date to calculate a future date. For example, as a project manager, you might need to add the project’s duration (in months) to its start date to forecast its end date.

In this tutorial, I will show you examples of how to add months to a date using Excel’s built-in functions and features.

Table of Contents

Method #1: Using the EDATE Function

You can use the EDATE function to add months to a date in Excel.

The EDATE function returns the serial number of a date that is a specified number of months before or after a given start date.

Note: Since the EDATE function returns a date as a serial number, you should apply a date format to the cell or cells where you use the function. This way, the serial number will display as a date.

The syntax of the EDATE function:

EDATE(start_date, months)

The function has two required arguments explained below.

start_date – This is the date representing the start date. In this case, it is the date to which you want to add months.
months – This is the number of months after or before the start date. A negative value for months results in a past date; a positive value yields a future date. In this case, it is the positive value for months you want to add to the start date to determine a future date.

Suppose you have the dataset below showing the start dates of some projects in column B and their duration (in months) in column C. You want to calculate in column D the end dates of the projects.

Here’s how to do it using the EDATE function:

Apply a date format to column D.
Enter the formula below in column D.

=EDATE(B2,C2)

The EDATE function adds the months in each row of column C to the corresponding start dates in column B and returns the projects’ end dates in column D.

Note: The EDATE function only works with whole months. If, for example, you supply 1.2 months for the month argument, the function adds 1 month to the start date and ignores the value after the decimal point.

Method #2: Using the DATE Function

To add months to a date in Excel, you can use the DATE function, which returns a serial number representing a specific date.

All you need to do is add the number of months to the month argument within the DATE function.

The DATE function creates a date based on the year, month, and day values you provide. The function returns the date as a serial number.

Note: Since the DATE function returns a date as a serial number, apply a date format to the cell or cells where you use the function to ensure the serial number is displayed as a date.

The syntax of the DATE function:

DATE(year,month,day)

The function has three required arguments that are explained below.

year – This is an integer representing the year. The integer can have one to four digits.
month – This is a positive or negative integer representing the month of the year from 1 to 12. You add months to this argument to add months to a date.
day – This is a negative or positive integer representing the day of the month from 1 to 31.

Suppose you have a dataset where column B contains the start dates of various projects, and column C shows their durations (in months). You want to calculate the end dates of the projects in column D.

Here’s how to do it using the DATE function:

Apply a date format to column D.
Enter the formula below in column D.

=DATE(YEAR(B2),MONTH(B2)+C2,DAY(B2))

The DATE formula adds the months in each row of column C to the corresponding start dates in column B and returns the projects’ end dates in column D.

Note: The DATE formula only works with whole months. If, for example, you adjust the month argument with 2.5 months, the formula adds 2 months to the start date and ignores the value after the decimal point.

Explanation of the Formula

=DATE(YEAR(B2),MONTH(B2)+C2,DAY(B2))

Here’s a breakdown of the formula:

YEAR(B2) – The YEAR function extracts the year from the start date in cell B2. For example, if the start date is January 15, 2024, the function returns 2024.
MONTH(B2)+C2 – This formula segment extracts the month from the start date in cell B2 and adds the duration (in months) from cell C2. For example, if the start date in cell B2 is January 15, 2024, and the duration in cell C2 is 6 months, the formula segment returns 7 (January + 6 months = July).
DAY(B2) – The DAY function extracts the day of the month from the start date in cell B2. For example, if the start date is January 15, 2024, the function returns 15.
=DATE(YEAR(B2),MONTH(B2)+C2,DAY(B2)) – The DATE function combines the year, the adjusted month (start month plus duration), and the day to create an end date. Using the example above, the end date would be July 15, 2024.

Also read: Add Days to a Date in Excel

Method #3: Using Power Query

You can use months to a date using Power Query.

Suppose you have the table below showing the start dates of various projects and their duration (in months). You want to determine the end dates of the projects.

Here’s how you can do it using Power Query:

Select any cell in the table.
Open the Data tab and click the ‘From Table/Range’ option on the Get & Transform Data group.

The above step loads the table data onto the Power Query Editor.

On the Power Query Editor, open the Add Column tab, and click the Custom Column option on the General group.

The above step opens the Custom Column feature.

Do the following on the Custom Column feature:

Type the title ‘End Date’ on the ‘New column name’ box.
Enter the formula =Date.AddMonths([Start Date],[#”Duration (Months)”]) on the ‘Custom column formula’ box and click OK.

Power Query uses the ‘Date.AddMonths’ function to add the number of months in the ‘Duration (Months)’ column to the corresponding dates in the ‘Start Date’ column and displays the results in the new ‘End Date’ column.

Power Query adds new column with months added to date

Open the Home tab and click the Close & Load option on the Close group.

The above step saves your changes to the query, closes the Power Query Editor window, and loads the results to a new worksheet, the default destination.

Notice that the end dates are displayed as serial numbers and the start dates are displayed in a different date format than the original.

You can select the Start Date and End Date columns and apply your desired date format.

Also read: Convert Date to Month and Year in Excel

Method #4: Using Fill Series Feature

You can use Excel’s Fill Series feature to add a specific number of months to a starting date, creating a list of dates evenly spaced by that interval of months.

Let’s say you’re planning bimonthly check-ins with a client for a year, starting from January 15, 2025. You need to create a list of dates evenly spaced by an interval of two months.

Here’s how you can do it:

Enter the start date in a cell, say cell B2.

Select cell B2 containing the start date and the cell range B3:B7 where you want the projected dates to be filled.

Click the Home tab, open the Fill drop-down list on the Editing group, and select the Series option.

The above step opens the Series dialog box.

Do the following on the Series dialog box:
- Select the Columns option on the ‘Series in’ Frame.
- Select the Date option on the ‘Type’ Frame.
- Select the Month option on the ‘ Date unit’ Frame.
- Enter 2 on the ‘Step value’ box.