Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
add comment

4 Answers 4

up vote 3 down vote accepted

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.

share|improve this answer
2  
+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
add comment

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.

share|improve this answer
2  
+1 for the fill handle (not for the rest!) –  pnuts May 23 '13 at 21:27
2  
Strict interpretation of the question makes the likes of February 31 irrelevant however. –  pnuts May 23 '13 at 21:45
    
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
    
You are certainly not the only one a lot less familiar with EDATE than with DATE - hence my choice. (DATE gets quite a lot of exposure from switching US/UK styles.) –  pnuts May 23 '13 at 21:58
1  
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
add comment

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.

share|improve this answer
add comment

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:

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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