Series of dates, increment 1 month

[Howard Kaikow]

Given a date in a cell named CurrentDate, I'd like to generate a series of
dates, each 1 month later than the previous date..

If I use

=DATE(YEAR(CurrentDate), MONTH(CurrentDate)+ROW()-2, DAY(CurrentDate))

And the start date is, say, 31 Oct 2007, then the dates get messed up for
months with less than 31 daze.

I was wondering whether any Excel date functions handle month increments
better than the above.
Otherwise, I'll write a VBA function to do this.

 

[Guest]

Howard

Is this what you want?

=IF(B1=EOMONTH(B1,0),EOMONTH(B1,1),B1+MONTH(B1)+1)

Regards
Peter

 

[Rick Rothstein \(MVP - VB\)]

The question is.... what date do **you** think is one month from 31 Oct 2007
is? Same question with 28 Feb 2007?

Rick

 

[Ron Rosenfeld]

Given a date in a cell named CurrentDate, I'd like to generate a series of
dates, each 1 month later than the previous date..

If I use

=DATE(YEAR(CurrentDate), MONTH(CurrentDate)+ROW()-2, DAY(CurrentDate))

And the start date is, say, 31 Oct 2007, then the dates get messed up for
months with less than 31 daze.

I was wondering whether any Excel date functions handle month increments
better than the above.
Otherwise, I'll write a VBA function to do this.

It depends on what you want for an answer.

Frequently, people want the series to not "spill over" into the succeeding
month. For example:

1/31/2007
2/28/2007
3/31/2007
4/30/2007
5/31/2007
6/30/2007
7/31/2007

Of course, if you start with 2/28, then you have:

2/28/2007
3/28/2007
4/28/2007
5/28/2007
6/28/2007
7/28/2007
8/28/2007


If that is acceptable, then try this formula:

=MIN(DATE(YEAR(CurrentDate),MONTH(CurrentDate)+ROWS($1:1)+{1,0},DAY(CurrentDate)*{0,1}))

and fill down as far as needed.

If you want a different kind of adjustment, you'll need to specify.

--ron

 

[Guest]

Hi
I had another look at it

=IF(A2=EOMONTH(A2,0),EOMONTH(A2,1),IF(A2<EOMONTH(A2,0),DATE(YEAR(A2),MONTH(A2)+1,DAY(A2))))

produces the following list:

31/10/2007 01/11/2007 02/11/2007
30/11/2007 01/12/2007 02/12/2007
31/12/2007 01/01/2008 02/01/2008
31/01/2008 01/02/2008 02/02/2008

Is this OK?
Peter

 

[Howard Kaikow]

I have not yet had a chance to look at th esuggested formulae.
Currently, I'm using

=nextmonth(CurrentDate, ROW(), ROW(CurrentDate))

Along with:

Option Explicit

Public Function NextMonth(StartDate As String, RowNum As Long, StartRow As
Long) As String
Application.Volatile True
NextMonth = Format(DateAdd("m", RowNum - StartRow, StartDate), "d mmm
yyyy")
End Function

 

[Guest]

Hello Billy,

....but if A2 is, for instance, 30th Jan 2007 then the next date produced by
that formula will be 2nd March 2007....which I presume is not the required
answer.
If using Analysis ToolPak functions is acceptable then, with first date in
A2 use this formula in A3 copied down

=EDATE(A$2,ROWS(A$3:A3))