Have you ever been required to find a specific date after some months like after 6 months or 12 months?
Excel has one inbuilt function EDATE which will help to do exactly the same.
In this tutorial, we will learn how to use a simple formula to add or subtract months to date in Excel.
Add Month to Date Using EDATE
Table of Contents
Suppose we have a dataset as shown as below and we want to add the given number of months in column B to the dates in Column A.
We will do this with Excel Function EDATE.
Syntax :
=EDATE(start_date,months)
- Start_Date : Start date is the date for which we want to get a certain number of months after or before it.
- Months : the number of months after or before the start date.if you want to subtract month, you have to enter a negative number in month column.
Let’s understand by example, we want to add 5 months to date 07.09.2021 and get the result in cell C2.
=EDATE(A2,B2)
The Result is 07-02-2021 which is the date after 5 months of 07-09-2021.
in our example, we have taken some of the values negatively. When used in this formula, this will give you the date which is the specific number of months ago in the past.
Note: the given formula will consider the integer part of the second argument. so if we enter 2.5 as the months to be added to the date, then also we will also get the date after 2 months .
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 Date(EDATE):
Using EDATE formula we can add years also not only months.
Let’s understand with one example, as per the below-given dataset where you want to add the number of years in column B to dates in column A.
Below is the formula for calculating:
=EDATE(A2,B2*12)
The result in C column is after 1 year of 07-09-2021. As you can see, the Parameter months is 12.
This way we can add or subtract years from the date.