How to Add Months to Date in Excel (Easy Formula)

Sometimes, when working with dates, you may want to know the date after a specific number of months.

For example, you may want to know the date three months after a specific date.

Thankfully, Excel has an in-built function (EDATE) that does exactly this.

In this tutorial, I will show you how to use a simple formula to add or subtract months to a date in Excel.

Add Months to a Date

Suppose you have a dataset as shown below and you want to add the given number of months in column B to the dates in column A.

Data with Start date and months to add

This can be done using the EDATE function.

Below is the syntax of the EDATE function:

=EDATE(start_date, months)
  • start_date – the date for which you want to get a certain number of months before or after it
  • months – the number of months before or after the start date. In case that you want to subtract months, you need to enter a negative number, since the function is by default adding months to a date.

In our example, we want to add 5 months (cell B2) to May 31, 2021 (A2) and get the result in cell C2.

The formula in C2 looks like:

=EDATE(A2, B2)
EDATE formula to add months to date

The result is October 31, 2021, as this is a date 5 months after May 31, 2021.

Note that you can use the same formula to add months as well as subtract months from any given date.

In our example, we have a couple of month values that are negative. When used in the formula, these will give you the date which is the specific number of months ago in the past.

Also, the formula will only consider the integer part of the second argument. So if enter 1.5 as the months to be added to the date, you will still get the date after one month (anything after the decimal is ignored).

Note: The prerequisite of the function is that columns containing a start date (column B) and a result date (D) are formatted as dates.

Add Years to a Date

Apart from adding months to a date, you can also use the EDATE function to add years.

Suppose you have a dataset as shown below where you want to add the number of years in column B to the dates in column A

Data to add years to date

Below is the formula that will do this:

=EDATE(A2, B2*12)
Formula to add years to date

The result in C2 is May 31, 2022, which is 1 year after May 31, 2021. As you can see, the parameter months is 12 (1 year * 12 months).

Also, since we are multiplying the month’s value with 12, you can also use decimal numbers as years and it should still work in most cases. For example, if you want to know the date after 1.5 years, you can do that (as 1.5*12 is 18, which is an integer).

This way, we can use the EDATE formula to also make the function to add/subtract years from a date.

So this is a simple formula method you can use to add months or years to a date in Excel.

I hope you found this tutorial useful!

Other Excel tutorials you may also like:

Excel Ebook Subscribe

FREE EXCEL BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster