Calculating Dates by Month

Calculating Dates by Month - please help!!!

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

GreenMan - 22 Jun 2004 12:38 GMT

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

--
Message posted from http://www.ExcelForum.com

Reply to this Message

Norman Harker - 22 Jun 2004 12:57 GMT

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.

Signature

Regards
Norman Harker MVP (Excel)
Sydney, Australia
njharker@optusnet.com.au

Reply to this Message

Trevor - 22 Jun 2004 21:03 GMT

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

> Hi GreenMan!
>
[quoted text clipped - 7 lines]
>
> 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
[quoted text clipped - 4 lines]
> Sydney, Australia
> njharker@optusnet.com.au

Reply to this Message