How to increment cells in a column by month

Question

Level 2

245 points

How to increment cells in a column by month

Is there a way that I can enter a date and increment subsequent cells in a column by 1 month and have the year incremented after the 12th month? Thanks.
Owen

1.83GHz Intel mini, PB G3 Series (wallstreet, OS 9.2), Mac OS X (10.4.11)

Posted on Oct 29, 2009 10:52 AM

Reply

Answer 1

KOENIG Yvan

Level 8

41,819 points

Oct 29, 2009 11:21 AM in response to Owen Farmer

My own tip

In A2 I enter
the formula:

=DATE(1999,ROW()-1,1)

Then I use fill down.
When I reach the 13th cell I get a blue triangle meaning "caution, the month number is illegal but I am able to work with it".
If you are annoyed by the blue triangle, use a more elaborated formula:

=DATE(1999 INT((ROW()-2)/12),1MOD(ROW()-2,12),1)

Yvan KOENIG (VALLAURIS, France) jeudi 29 octobre 2009 19:21:40

Reply

Answer 2

Owen Farmer Author

Level 2

245 points

Oct 29, 2009 12:08 PM in response to KOENIG Yvan

Great solution. Probably should also note that
=DATE(1999,ROW()-1,1)
starts in January. To start in December, for example,
=DATE(1999,ROW()+10,1)
Thanks,
Owen

Reply

Answer 3

Oct 29, 2009 1:20 PM in response to Owen Farmer

Owen,

I believe that the most elegant way to make a list of dates incrementing by months is to type the first date, followed by the second date. Then select both dates and drag the Fill handle down as far as you need. There is no conflict with year rollover.

Jerry

Reply

Answer 4

Owen Farmer Author

Level 2

245 points

Oct 29, 2009 3:08 PM in response to Jerrold Green1

I just tried this for increments of 1 week, and also 1 month in both rows and columns and it worked. Very easy. Thanks,
Owen

Reply

Answer 5

Oct 29, 2009 7:40 PM in response to Owen Farmer

Owen,

Thanks for letting me know what worked for you.

Jerry

Reply