ExcelBanter - List of start of months

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - List of start of months (https://www.excelbanter.com/excel-worksheet-functions/26708-list-start-months.html)

Daniel Bonallack

List of start of months

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

Peo Sjoblom

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

Daniel Bonallack

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

All times are GMT +1. The time now is 05:23 PM.