Add Months to Date using EDATE Function (Step by Step with Examples)
In excel we have a built-in function called EDATE which adds specified number of months to the supplied date returns the next specified month for the same day. Before I show you how to use EDATE in excel, let me introduce you to the syntax of EDATE function.
Assume you have taken a TV on EMI for 6 months. EMI will be deducted on the 05th of every month. Now you need to create an EMI chart with the same date for every month. The first EMI is on 05-02-2019.
In the next five rows we need 05th Mar 2019, 05th Apr 2019 and so on for the next 5 months.
Step 1: Open the EDATE function in the B2 cell.
Step 2: Start date is our above month i.e. B2 cell month.
Step 3: The next thing is how many months we need to add i.e. 1 month so supply 1 as the argument.
Step 4: Yes we have got the next month change but not the date here. Fill down the formula to the remaining cells to have all the month’s date as 5th.
Top 5 Useful Examples
Example #1 – Different Result in Case of February
Assume you had taken the loan and EMI due is on the 30th of every month. First will be on 30th Oct 2018 and EMI is for 6 months. Let’s apply the EDATE function to arrive at all the month’s due date.
If you notice the above function the first thing is the moment it ends the year 2018 in Dec it has automatically jumped to the next year i.e. 2019 (refer C5 cell).
The second thing is in February 2019 which is a non-leap year, has only 28 days. So the formula has returned the due date as 28th Feb 2019.
Example #2 – Special Due Date for Leap Year
Now take an example of the year which has a leap year in it. In case of a leap, year formula will return 29th Feb, not the 28th Feb.
Example #3 – Get Previous Months with Negative Number
We have learned how to get next month’s date from the current date. What if we need to get the previous months from the current date?
Assume we have the starting date as 05th Sep 2018 and we need to go back for 6 months.
Apply EDATE function but in the no., of months to add mention -1 as the argument.
Example #4 – Other Ways to Add Months to Date in Excel
We can months to days by using other methods as well. This is a bit complicated method but just to have knowledge I am showing this to you.
Here DATE function extracts the year, month, and day from the above but only thing we have done here we are adding +1 to the month for all the cells.
Example #5 – EDATE with Other Functions
We can use EDATE with other functions as well. Assume you want to count the number of invoices generated from a certain date to a certain date, we need to use EDATE function.
I have below formula to count invoices from Count No., of Invoices from 17th Nov 2018 to 16th Dec 2018.
Things to Remember
- In the number of months, you can add any number. If the date is falling in next year it will automatically change the year as well.
- If leap year comes Feb last date will be 29th, if not it will be 28th.
- A positive number will give future months and negative numbers will give previous months.
- If the date format is not there for the starting date then we will get the #VALUE error.
This has been a guide to Add Months to Date in Excel. Here we discuss how to Add Months to Date in Excel using by EDATE Formula along with practical examples and downloadable excel template. You may learn more about excel from the following articles –