Calculating Dates by Month - please help!!!

G

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!!!!
:
 
N

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.
 
T

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top