Formula = Today's date + 1 month

[jermsalerms]

I am trying to do the following in A1:480


A1 = today's date + 1 month
A2 = A1 + 1 month
A3 = A2 + 1 month
A4 = A3 + 1 month

continue that to cell A480

I know that TODAY() will enter todays date but Im not sure how to get it to read the + 1 month part.

help...thanks

[daddylonglegs]

The easiest way is to use the EDATE function, for which you need Analysis ToolPak enabled

in A1

=EDATE(TODAY(),1)

in A2 copied down

=EDATE(A1,1)

note that with EDATE if today is 31st March EDATE(TODAY(),1) will give 30th April

edit: in order to avoid the situation which the above would cause....if today is 31st january 2006

A1 = 28th Feb 2006
A2 = 28th Mar 2006
A3 = 28th April 2006 etc.

use in A1 copied down

=EDATE(TODAY(),ROW()-ROW(A$1)+1)

then you get

A1 = 28th Feb 2006
A2 = 31st March 2006
A3 = 30th April 2006 etc.

[Gary''s Student]

If A1 =TODAY()
then =DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))
puts you 1 month later
--
Gary's Student


"jermsalerms" wrote:

>
> I am trying to do the following in A1:480
>
>
> A1 = today's date + 1 month
> A2 = A1 + 1 month
> A3 = A2 + 1 month
> A4 = A3 + 1 month
>
> continue that to cell A480
>
> I know that TODAY() will enter todays date but Im not sure how to get
> it to read the + 1 month part.
>
> help...thanks
>
>
> --
> jermsalerms
> ------------------------------------------------------------------------
> jermsalerms's Profile: http://www.excelforum.com/member.php...o&userid=30167
> View this thread: http://www.excelforum.com/showthread...hreadid=509608
>
>

[daddylonglegs]

If A1 =TODAY()
then =DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))
puts you 1 month later

..but of course if today is 31st january 2006

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

gives you 3rd March 2006

[Ron Rosenfeld]

On Tue, 7 Feb 2006 18:37:18 -0600, daddylonglegs
<[email protected]> wrote:

>
>The easiest way is to use the EDATE function, for which you need
>Analysis ToolPak enabled
>
>in A1
>
>=EDATE(TODAY(),1)
>
>in A2 copied down
>
>=EDATE(A1,1)
>
>note that with EDATE if today is 31st March EDATE(TODAY(),1) will give
>30th April

The problem with doing it this way is that when you get near the end of a
month, other dates may not be what the OP really wants. For example, on 28-31
Jan 2006, the subsequent dates will be limited to the 28th of the succeeding
months (until 1 Feb 2006).

1/31/2006
2/28/2006
3/28/2006
4/28/2006
5/28/2006
6/28/2006
7/28/2006

If end of month dates are preferable, then, in a1 use the formula:

=edate(TODAY(),ROWS($1:1))

and copy down. Then you get:

1/31/2006
2/28/2006
3/31/2006
4/30/2006
5/31/2006
6/30/2006



--ron

[daddylonglegs]

Thanks ron,

I had realised that and edited my post accordingly to suggest something similar, i.e.

=EDATE(TODAY(),ROW()-ROW(A$1)+1)

(if starting in row 1)