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.
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)
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.
Also read: How to Add Week to Date in Excel?
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
Below is the formula that will do this:
=EDATE(A2, B2*12)
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:
- Combine Date and Time in Excel (Easy Formula)
- How to Get Month Name from Date in Excel
- How to Add or Subtract Days to a Date in Excel (Shortcut + Formula)
- How to Convert Serial Numbers to Dates in Excel
- Calculate Quarter from Date in Excel
- Calculate the Number of Months Between Two Dates in Excel
- How to Calculate Years of Service in Excel (Easy Formulas)
- How to Compare Dates in Excel (Greater/Less Than, Mismatches)
