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:



produces 3/25/2013 which is wrong.


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


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.

  • 4
    +1 for EDATE - Analysis ToolPak is only required for Excel 2003 or earlier Excel versions - in later versions EDATE is a built-in function – barry houdini May 23 '13 at 21:31
  • @barryhoudini Didn't know of that, since I have Analysis Toolpak (& VBA) enabled all the time for other statistical functions. – laika May 24 '13 at 11:02
  • @barryhoudini BTW do you know how can I recognize on any function whether it is from standard or particular add-in, and if so which add-in it belongs to? – laika May 24 '13 at 11:03

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.

  • 1
    That's a good point about making February 31 irrelevant. Also, I hadn't realized EDATE() was a built-in function, so that would certainly be the simpler formula in this case. – Nicholas Cole May 23 '13 at 21:51
  • 2
    You don't need to accommodate December separately - pnuts suggestion works for any month because, for example, Excel recognises =DATE(2010,13,1) as 1st January 2011 – barry houdini May 23 '13 at 22:51

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!

  • 1
    This has the same problem as many of these other solutions in dealing with dates after the 28th. Try it with 1/30/16. – Bandersnatch Aug 5 '17 at 19:47

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.


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

Another option is this:


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

  • 1
    (1) The OP reported trying the obvious, simple +30 answer, and that it failed. (2) It failed for you, too.  The OP wants the February date to be 2/ 23 /2013; your formula produces 2/ 22 /2013. – G-Man Says 'Reinstate Monica' Aug 6 '17 at 7:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.