Add Months to Date using EDATE Function (Step by Step with Examples)

=EDATE(start_date, months)

Assume you have taken a TV on EMI for six months. In addition, EMI will be deducted on the 05th of every month. Therefore, you need to create an EMI chart with the same date every month. For example, the first EMI is on 05-02-2019.

Top 5 Useful Examples

Example #1 – Different Results in Case of February

Assume you had taken the loan and EMI due is on the 30th of every month. The first will be on 30th Oct 2018, and EMI is for six months. Let us apply the EDATE function to arrive at all the month’s due dates.

  • We must first open the EDATE function in the B2 cell. Then, the start_date is our above month, i.e., B2 cell month. The next thing is how many months we need to add, i.e., one month, so we must supply 1 as the argument. Yes, we have the next month’s change but not the date here. So we will fill down the formula to the remaining cells to have all the month’s dates as 5th.

If you notice the above function, the first thing is when it ends the year 2018 in Dec. After that, it automatically jumped to the next year, i.e., 2019 (refer to C5 cell).

The second thing is in February 2019, which is a non-leap year. So, it 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, we will take an example of a year that has a leap year. In case of a leap, the, year formulaYear FormulaThe year function in excel is a date function to calculate the year from a given date. This function takes a serial number as an argument and returns a four-digit numeric value representing the year of the given date, formula = year (serial number)read more will return on 29th Feb, not the 28th Feb.

Example #3 – Get Previous Months with a 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 six months.

We must apply the 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 take months to days by using other methods as well. It is a complicated method, but we show this to you.

Here, the DATE function in excelDATE Function In ExcelThe date function in excel is a date and time function representing the number provided as arguments in a date and time code. The result displayed is in date format, but the arguments are supplied as integers.read more extracts the year, month, and day from the above. So, the only thing we have done here is adding +1 to the month for all the cells.

Example #5 – EDATE with Other Functions

We can use the EDATE with other functions as well. For example, assume we want to count the number of invoices generated from a certain date to a certain date. We need to use the EDATE functionEDATE FunctionEDATE is a date and time function in excel which adds a given number of months into a date and gives us a date in a numerical format of date. It takes dates and integers as input, the output returned by this function is also a date value. read more.

We have the 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, we can add any number. If the date falls next year, it will automatically change the year.If leap year comes, Feb last date will be the 29th. If not, it will be the 28th.A positive number will give future months, and a negative number will give previous months.If the date format is not there for the starting date, we will get the #VALUE! Error.

This article has been a guide to Add Months to Date in Excel. We discuss adding months to date in Excel using EDATE Formula, practical examples, and a downloadable Excel template. You may learn more about Excel from the following articles: –

  • Excel Date FormulaDate Excel FormatConvert Date to Text in ExcelInsert Date in Excel