Excel Add Months to Dates (Table of Contents)
Add Months to Dates in Excel
Adding months to date in Excel becomes very easy when we use the “EDATE” function. It is a built-in function that can be used to add months to any date.
What is EDATE function in Excel?
EDATE is a built-in function in Excel. It falls under the Date and Time functions category in Excel. So, what it is used for is to get the same date of the month ‘Y’ months in the future or in the past. So basically, it returns the serial number of the date which is the specified number of months prior or subsequent to the mentioned start date. EDATE Function becomes highly useful while calculating the expiration dates of products, maturity dates of policies, or other types of due dates. To get a date from the future, just provide a positive month value and in order to get a date from the past, just provide a negative month value.
Syntax of EDATE Function in Excel
The Syntax of EDATE is shown below:
There are two arguments for EDATE function in Excel. Both arguments are mandatory.
- start_date: This has to be a valid date that represents the start date. It has to be in a valid Excel serial number format.
- Months: This is the argument that denotes the number of months, either in the past or in the future from the start_date, based on whether it has a positive or negative value.
How to Use the EDATE Function to Add Months to Dates in Excel?
EDATE Function in Excel is a typical worksheet function, like all Worksheet functions, EDATE too can be entered in a cell of a worksheet as a part of the formula. Let us understand how to add months to dates in Excel through some examples.
Each example will try to explore a different use case which is implemented using the EDATE function.
Add Months to Dates in Excel – Example #1
Get the same date of two months in the future
The above example shows that EDATE(A2,2) gives us a value of two months into the future with respect to the start date provided in the cell A2. Cell A2 has a date – 15th November 2018.
Two months into the future would bring us to 15th January 2019. But as of now, we have a serial number which is not easily understandable. Hence, we will now attempt to change the format of this date value.
To do that first, we will select this cell B2 and then right-click. In the menu that opens up, we will select Format Cell option.
Next, we will click on Date in the category section and then select the desired Date Type and click OK to apply the changes.
Now our changes should be reflected, and we get the EDATE result in the format shown below.
This is exactly what we had calculated theoretically. We have 15th of January 2019 which is 2 months after the start date provided.
Add Months to Dates in Excel – Example #2
Adding Months to given Date
In the above example, we have the source dates defined in Column A (from A2 to A4). The source date is in MM/DD/YYYY format, which is a valid Excel date.
We have the “months” that is to be added or subtracted in order to go to the past or future, defined in Column B (from B2 to B4).
Now we have our results in Column C (from cells C2 to C4). We are using the EDATE function to calculate the results.
The formula used for each case is shown below:
- C2: =EDATE(A2,B2)
- C3: =EDATE(A3,B3)
- C4: =EDATE(A4,B4)
But as of now, we have a serial number which is not easily understandable.
Hence, we will now attempt to change the format of this date value.
To do that, first, we will select this cell C2 and then right-click. In the menu that opens up, we will select the Format Cell option.
Next, we will click on Date in the category section and then select the desired Date type and click OK to apply the changes.
Now our changes should be reflected, and we get the EDATE result in the format shown below.
Now we will drag this format to the remaining Result cells (C3, C4) by clicking on Format Painter while we have selected cell C2 and then pasting the format to cells C3 and C4.
So, we see that our results are in line with our expectations. Cell C2 evaluated to 5-15-2019 which is 12 months after the source date 5-15-2018. Similarly, cell C3 evaluated to 8-5-2018 which is 5 months before the source date 1-5-2019. Cell C4 evaluated to 2-15-2019 which is 240 months or 20 years after the source date 2-15-1999.
Add Months to Dates in Excel – Example #3
Figure out Retirement date from Birthdate
In the example shown above, we have a Birth date, defined in Column A (from A2 to A3). The Birthdate is in MM/DD/YYYY format, which is a valid Excel date. To calculate the retirement date, we use the EDATE function as shown below
Here we have taken 60 as the retirement age in years and we multiply it by 12 to get the number of months to be added. Now in the first case, 60*12 gives us 720 months, which is to be added to the birth date i.e. 8/11/1991. This gives us 8/11/2051.
To calculate the years left until retirement, we use the YEARFRAC function
YEARFRAC returns the year fraction representing the number of full days between the end date and the start date. YEARFRAC accepts two arguments – start date and end date.
In this case, the start date is today’s date and our end date is the date of retirement in Column B (cells B2,B3). The Excel function TODAY() returns today’s date in date format. Hence we use TODAY() as the start date and the end date as the retirement date.
After that YEARFRAC gives us the difference in fractions, which in the first case is: 2/15/2019 (minus) 8/11/2051 i.e. 32 years and 5 months and 27 days or 32.455 years.
In the example shown above, we have the source date i.e. the birth dates defined in Column A (from cells A2 to A3). We have the retirement date calculated in Column B (from cells B2 to B3) and finally we have the number of years left until retirement calculated in Column C (from cells C2 to C3).
Add Months to Dates in Excel – Example #4
Calculate the Expiration Date
In the above example, we have the start date defined in Column A (cell A2). The duration is defined in Column B (cell B2). The duration is 30 days or 1 month.
Hence the expiry date is one month after the start date 04/03/2018 (3rd April 2018), which is 3rd May 2018. We get this result using the EDATE function as shown below:
Things to Remember
- The second argument of this EDATE function must always be positive.
- EDATE will return #VALUE! error if the start date is an invalid date.
- If the “months” is not an integer, then the truncated integer value is considered.
- Excel by default stores dates as sequential serial numbers. This is done so as to make using dates for calculations easier. The default start date is 1st January 1900 which has a serial number of 1. All dates after this have incremental values. For example – 1st January 2001 is 36892.
Recommended Articles
This has been a guide to Add Months to Dates in Excel. Here we discussed How to Add Months to Dates in Excel and what are the uses of Adding Months to Dates in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles–
