I would like to be able to put a date in Cell A1 (say April 18, 2005)

Then, after recalculating, in cells B1:B24 would be:
03/01/05
02/01/05
01/01/05
12/01/04
etc
- in other words, the first of the month going back 24 months.

Can someone give me a formula for this?

Thanks!
Daniel

One way

in B1 put

=DATE(YEAR(A1),MONTH(A1)-1,1)

in B2

=DATE(YEAR(B1),MONTH(B1)-1,1)

copy down

or in B1

=DATE(YEAR($A$1),MONTH($A$1)-ROW(B1),1)

copy down



Regards,

Peo Sjoblom

"Daniel Bonallack" wrote:

I would like to be able to put a date in Cell A1 (say April 18, 2005)

Then, after recalculating, in cells B1:B24 would be:
03/01/05
02/01/05
01/01/05
12/01/04
etc
- in other words, the first of the month going back 24 months.

Can someone give me a formula for this?

Thanks!
Daniel

Thank you very much!

Daniel


"Peo Sjoblom" wrote:

One way

in B1 put

=DATE(YEAR(A1),MONTH(A1)-1,1)

in B2

=DATE(YEAR(B1),MONTH(B1)-1,1)

copy down

or in B1

=DATE(YEAR($A$1),MONTH($A$1)-ROW(B1),1)

copy down



Regards,

Peo Sjoblom

"Daniel Bonallack" wrote:

I would like to be able to put a date in Cell A1 (say April 18, 2005)

Then, after recalculating, in cells B1:B24 would be:
03/01/05
02/01/05
01/01/05
12/01/04
etc
- in other words, the first of the month going back 24 months.

Can someone give me a formula for this?

Thanks!
Daniel