How to add/subtract months from given date in Excel

Question

Here is my scenario:

I have to populate list with dates and some metadata of upcoming events for the following year in my Excel application. Some of the events are occurring on the same day each month, e.g. the 3rd day of month.

So the question is:

Is there any function so that I can add one month to given date, like:

• cell A1: 1/23/2013
• cell A2: 2/23/2013
• cell A3: 3/23/2013

What did not work:

Apparently

A3=$A2+30

produces 3/25/2013 which is wrong.

Answer 1

As far as I did not found answer here on superuser I post my own answer:

According to this link Excel: Adding/Taking n Month(s) to a Date. Add Months to an Excel Date you have to:

• Enable Analysis Toolpak
  • File -> Options -> Add-ins -> Manage: Excel Add-ins -> Go

and use the function EDATE(reference_cell, offset_integer), here's an example:

• A2 = 2/23/2013
• A3 = EDATE($A1, 1) -> 3/23/2013

EDIT:

As barry houdini pointed out in comment, Analysis Toolpak is needed only for Excel 2003 and earlier, as far as EDATE function is built-in in later versions of Excel.

Answer 2

You have two possible approaches that I can think of. The first is the fill handle:

1. In cell A1, enter 1/23/2013.
2. In cell A2, enter 2/23/2013.
3. Highlight cells A1 and A2.
4. Place your mouse on the fill handle at the bottom right of the group of highlighted cells, and drag down for as many values as you need.

The second, as discussed already, is formulas, but you need to account for the month of December:

=IF(MONTH($A1) = 12, DATE($A1 + 1, 1, DAY($A1)), DATE(YEAR($A1), MONTH($A1) + 1, DAY($A1)))

1. If the month is December, compose a new date, DATE(), with the following values:
   1. The original year incremented by 1.
   2. The month of January.
   3. The original day of the month.
2. Else compose a new date, DATE(), with the following values:
   1. The original year.
   2. The original month incremented by 1.
   3. The original day of the month.

Note that you still need to validate the day of the month. For instance, January 31 is a valid date, but February 31 wouldn't be. That would be a little more complicated, however, and depends on whether you want to cover every edge case.

Answer 3

I realised this answer is a few years too late... but this works for any date, just in case anyone comes here looking like I did :)

A1 = 2017/8/6
A2 = =EOMONTH(A1,0)+DAY(A1)

EOMONTH takes you to the end of the month (in this case 2017/8/31) and the DAY formula gives you the number of days since the start of the month (in this case 6 since it's the 6th of August). Add them together and you're done.

The second part of EOMONTH determines how many months ahead you go, 0 is the end of the referenced month, 1 is the end of the month after the referenced month and so on...

Hope someone finds this helpful!

Answer 4

I think =DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)) (in A2 and copied down) works also.

Edit But may fall down for dates after the 28th.

Answer 5

I was able to add 30 to the date. Cell A1: 1/23/2013 and B1: =A1+30 and it produced 2/22/2013.

Another option is this:

=DATE(YEAR(A1),MONTH(A1)+MONTHSTOADD,DAY(A1))

Where A1 is your original date and MONTHSTOADD is the number of months you want to add.

((Sorry, the second option I added appears to have also been added by another user.))