Calculating Dates by Month - please help!!!

[GreenMan]

I'm pretty new to this game, so here goes.....

If I enter a date in cell A1, say 24/11/03.... I then want B1 to tel
me what month that is (i.e November-03)

But I also want excel to automatically complete cells C1-M1 with th
next 11 months (December-03 to October-04).

Thanks in advance!!!!
:

 

[Norman Harker]

Hi GreenMan!

First your format problem

Select A1
Format > Cells > Custom Format
type mmmm-yy in the text box.

Now copying across:

Use the following in B1:
=DATE(YEAR(A1),MONTH(A1)+1,MIN(DAY($A$1),DAY(DATE(YEAR(A1),MONTH(A1)+2,0))))

Watch out for other solutions because they can fall down when copying
across or down where the base month day is >=29. You find with the
above format that this will result in skipping a month.

 

[Trevor]

GreenMan,

In cells B1 through M1, apply the cell formatting as Norman recommends.

In cell B1 enter the following formula to get the month of the date you
entered in A1:
=DATE(YEAR(A1),MONTH(A1),1)

In cell C1, enter the following formula, to add one month as you go across
the columns:
=DATE(YEAR(B1),MONTH(B1)+1,1)

Trevor