Excel Add Months to Date

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.

=EDATE(start_date, months)

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.

Add Months to Date in Excel Step 1

In the next five rows we need 05th Mar 2019, 05th Apr 2019 and so on for the next 5 months.

  1. Open the EDATE function in the B2 cell.

    Add Months to Date in Excel Step 2

  2. Start date is our above month i.e. B2 cell month.

    Add Months to Date in Excel Step 3

  3. The next thing is how many months we need to add i.e. 1 month so supply 1 as the argument.

    EDATE Function Step 4

  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.

    EDATE Function Step 5

Top 5 Useful Examples

You can download this Add Months to Date Excel Template here – Add Months to Date Excel Template

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).

Different Result in case of february

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 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 29th Feb, not the 28th Feb.

add month to Leap Year

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.

Get Previous Months 1

Apply EDATE function but in the no., of months to add mention -1 as the argument.

Get Previous Months 2

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.

Other Ways to Add Months

Here 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 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 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.

EDATE with Other Functions 1

I have below formula to count invoices from Count No., of Invoices from 17th Nov 2018 to 16th Dec 2018.

EDATE with Other Functions 2

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.

Recommended Articles

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 –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion