Updated June 9, 2023
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 add months to any date.
What is the EDATE function in Excel?
EDATE is a built-in function in Excel. It falls under the Date and Time functions category in Excel. So, it is used to get the same date of the month ‘Y’ months in the future or the past. So basically, it returns the serial number of the date, which is the specified number of months before or after 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; 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 the EDATE function in Excel. Both arguments are mandatory.
- start_date: This must be a valid date representing the start date. It has to be in a valid Excel serial number format.
- Months: This argument 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; You can enter EDATE as a part of the formula in a worksheet cell, just like all other Worksheet functions. Let us understand how to add months to dates in Excel through some examples.
Each example will try to explore a different use case that 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 to the start date provided in cell A2. Cell A2 has a date – of 15th November 2018.
Two months into the future would bring us to 15th January 2019. But as of now, we have a serial number that is not easily understandable. Hence, we will now attempt to change the format of this date value.
To do that, first, we will select cell B2 and then right-click. In the open menu, we will select the Format Cell option.
Next, we will click on Date in the category section, select the desired Date Type, and click OK to apply the changes.
Our changes should be reflected, and we will get the EDATE results in the format below.
This is exactly what we had calculated theoretically. We have the 15th of January 2019, 2 months after the start date provided.
Add Months to Dates in Excel – Example #2
Adding Months to given Date
The above example defines the source dates in Column A (from A2 to A4). The source data is valid Excel date in MM/DD/YYYY format.
We have the “months” to add or subtract 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 that is not easily understandable.
Hence, we will now attempt to change the format of this date value.
To do that, first, we will select cell C2 and then right-click. In the open menu, we will select the Format Cell option.
Next, we will click on Date in the category section, select the desired Date type, and click OK to apply the changes.
Our changes should be reflected, and we will get the EDATE results in the format 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 was evaluated on 5-15-2019, 12 months after the source date of 5-15-2018. Similarly, cell C3 was evaluated on 8-5-2018, 5 months before the source date of 1-5-2019. Cell C4 was evaluated on 2-15-2019, 240 months or 20 years after the source date of 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 a valid Excel date in MM/DD/YYYY format. To calculate the retirement date, we use the EDATE function, as shown below.
Here we have taken 60 as the retirement age in years, multiplying it by 12 to get the number of months to add. In the first case, 60*12 gives us 720 months, adding 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 and start dates. YEARFRAC accepts two arguments – a start date and an end date.
In this case, the start date is today’s date, and our end date is the retirement date 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 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 of 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 invalid.
- 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 to make using dates for calculations easier. The default start date is 1st January 1900, with 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 the uses of Adding Months to Dates in Excel, practical examples, and a downloadable Excel template. You can also go through our other suggested articles–
