I have the following table:

Jan-08
Feb-07
Mar-07
Apr-07
May-07
Jun-07
Jul-07
Aug-07
Sep-07
Oct-07
Nov-07
Dec-07

Is there a formula that when we enter a new month, in this case Feb 07 it
will change to Feb 08 and as we enter the following month in turn it up dates
to 08 each time?
--
capt

Here is an example of monthly updating:

In A1 enter:
39083

In A2 enter:
=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)) and copy this formula down as far as
you like.

Select column A and:

Format Cells... Number Custom mmm-yy
--
Gary''s Student - gsnu200762


"capt" wrote:

I have the following table:

Jan-08
Feb-07
Mar-07
Apr-07
May-07
Jun-07
Jul-07
Aug-07
Sep-07
Oct-07
Nov-07
Dec-07

Is there a formula that when we enter a new month, in this case Feb 07 it
will change to Feb 08 and as we enter the following month in turn it up dates
to 08 each time?
--
capt

Thanks Gary,
The trouble its changing every date at once. Is there a way in order that
the year changes when the month actually starts. In other words: because we
are in the month of January cell A2 changes to Jan 08 the rest remain
indicating 07 year. Then as we reach the end of Jan and go into Febuary then
it changes to Feb 08 and it carrys on changing as we reach that month for
real.
I hope that makes sense?
--
capt


"Gary''s Student" wrote:

Here is an example of monthly updating:

In A1 enter:
39083

In A2 enter:
=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)) and copy this formula down as far as
you like.

Select column A and:

Format Cells... Number Custom mmm-yy
--
Gary''s Student - gsnu200762


"capt" wrote:

I have the following table:

Jan-08
Feb-07
Mar-07
Apr-07
May-07
Jun-07
Jul-07
Aug-07
Sep-07
Oct-07
Nov-07
Dec-07

Is there a formula that when we enter a new month, in this case Feb 07 it
will change to Feb 08 and as we enter the following month in turn it up dates
to 08 each time?
--
capt

Put this formula...

=DATE(YEAR(NOW())-(MONTH(NOW())<ROWS($1:1)),ROWS($1:1),1)

in the cell where your January date is supposed to be, copy it down through
the next 11 cells and, finally, apply the Mar-01 date format from the Format
Cells option to those 12 cells.

Rick


"capt" wrote in message
...
Thanks Gary,
The trouble its changing every date at once. Is there a way in order that
the year changes when the month actually starts. In other words: because
we
are in the month of January cell A2 changes to Jan 08 the rest remain
indicating 07 year. Then as we reach the end of Jan and go into Febuary
then
it changes to Feb 08 and it carrys on changing as we reach that month for
real.
I hope that makes sense?
--
capt


"Gary''s Student" wrote:

Here is an example of monthly updating:

In A1 enter:
39083

In A2 enter:
=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)) and copy this formula down as far as
you like.

Select column A and:

Format Cells... Number Custom mmm-yy
--
Gary''s Student - gsnu200762


"capt" wrote:

I have the following table:

Jan-08
Feb-07
Mar-07
Apr-07
May-07
Jun-07
Jul-07
Aug-07
Sep-07
Oct-07
Nov-07
Dec-07

Is there a formula that when we enter a new month, in this case Feb 07
it
will change to Feb 08 and as we enter the following month in turn it up
dates
to 08 each time?
--
capt

Rick,
Fantanstic, it works well.
Happy new year!
--
capt


"Rick Rothstein (MVP - VB)" wrote:

Put this formula...

=DATE(YEAR(NOW())-(MONTH(NOW())<ROWS($1:1)),ROWS($1:1),1)

in the cell where your January date is supposed to be, copy it down through
the next 11 cells and, finally, apply the Mar-01 date format from the Format
Cells option to those 12 cells.

Rick


"capt" wrote in message
...
Thanks Gary,
The trouble its changing every date at once. Is there a way in order that
the year changes when the month actually starts. In other words: because
we
are in the month of January cell A2 changes to Jan 08 the rest remain
indicating 07 year. Then as we reach the end of Jan and go into Febuary
then
it changes to Feb 08 and it carrys on changing as we reach that month for
real.
I hope that makes sense?
--
capt


"Gary''s Student" wrote:

Here is an example of monthly updating:

In A1 enter:
39083

In A2 enter:
=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)) and copy this formula down as far as
you like.

Select column A and:

Format Cells... Number Custom mmm-yy
--
Gary''s Student - gsnu200762


"capt" wrote:

I have the following table:

Jan-08
Feb-07
Mar-07
Apr-07
May-07
Jun-07
Jul-07
Aug-07
Sep-07
Oct-07
Nov-07
Dec-07

Is there a formula that when we enter a new month, in this case Feb 07
it
will change to Feb 08 and as we enter the following month in turn it up
dates
to 08 each time?
--
capt

Just one more question Rick,
When I reach Dec-08, will Jan-08, at the top of the list, change to Jan-09
or do I have to continue with the list down as far as I wish?
--
capt


"capt" wrote:

Rick,
Fantanstic, it works well.
Happy new year!
--
capt


"Rick Rothstein (MVP - VB)" wrote:

Put this formula...

=DATE(YEAR(NOW())-(MONTH(NOW())<ROWS($1:1)),ROWS($1:1),1)

in the cell where your January date is supposed to be, copy it down through
the next 11 cells and, finally, apply the Mar-01 date format from the Format
Cells option to those 12 cells.

Rick


"capt" wrote in message
...
Thanks Gary,
The trouble its changing every date at once. Is there a way in order that
the year changes when the month actually starts. In other words: because
we
are in the month of January cell A2 changes to Jan 08 the rest remain
indicating 07 year. Then as we reach the end of Jan and go into Febuary
then
it changes to Feb 08 and it carrys on changing as we reach that month for
real.
I hope that makes sense?
--
capt


"Gary''s Student" wrote:

Here is an example of monthly updating:

In A1 enter:
39083

In A2 enter:
=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)) and copy this formula down as far as
you like.

Select column A and:

Format Cells... Number Custom mmm-yy
--
Gary''s Student - gsnu200762


"capt" wrote:

I have the following table:

Jan-08
Feb-07
Mar-07
Apr-07
May-07
Jun-07
Jul-07
Aug-07
Sep-07
Oct-07
Nov-07
Dec-07

Is there a formula that when we enter a new month, in this case Feb 07
it
will change to Feb 08 and as we enter the following month in turn it up
dates
to 08 each time?
--
capt

No, it will not. When the date is January 1, 2009 is when the Jan-08 will
change. Were you after some other functionality?

Here is a formula that will show you how the display will look for various
dates. Put this in some cell (other than C1) and copy down...

=DATE(YEAR($C$1)-(MONTH($C$1)<ROWS($1:1)),ROWS($1:1),1)

Just type various dates into C1 and watch how the display changes for them.
If you need a different type of display, just let us know.

Rick


"capt" wrote in message
...
Just one more question Rick,
When I reach Dec-08, will Jan-08, at the top of the list, change to Jan-09
or do I have to continue with the list down as far as I wish?
--
capt


"capt" wrote:

Rick,
Fantanstic, it works well.
Happy new year!
--
capt


"Rick Rothstein (MVP - VB)" wrote:

Put this formula...

=DATE(YEAR(NOW())-(MONTH(NOW())<ROWS($1:1)),ROWS($1:1),1)

in the cell where your January date is supposed to be, copy it down
through
the next 11 cells and, finally, apply the Mar-01 date format from the
Format
Cells option to those 12 cells.

Rick


"capt" wrote in message
...
Thanks Gary,
The trouble its changing every date at once. Is there a way in order
that
the year changes when the month actually starts. In other words:
because
we
are in the month of January cell A2 changes to Jan 08 the rest remain
indicating 07 year. Then as we reach the end of Jan and go into
Febuary
then
it changes to Feb 08 and it carrys on changing as we reach that month
for
real.
I hope that makes sense?
--
capt


"Gary''s Student" wrote:

Here is an example of monthly updating:

In A1 enter:
39083

In A2 enter:
=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)) and copy this formula down as
far as
you like.

Select column A and:

Format Cells... Number Custom mmm-yy
--
Gary''s Student - gsnu200762


"capt" wrote:

I have the following table:

Jan-08
Feb-07
Mar-07
Apr-07
May-07
Jun-07
Jul-07
Aug-07
Sep-07
Oct-07
Nov-07
Dec-07

Is there a formula that when we enter a new month, in this case
Feb 07
it
will change to Feb 08 and as we enter the following month in turn
it up
dates
to 08 each time?
--
capt

No thats fine Rick, your first code will do nicely.
Thank very much.
--
capt


"Rick Rothstein (MVP - VB)" wrote:

No, it will not. When the date is January 1, 2009 is when the Jan-08 will
change. Were you after some other functionality?

Here is a formula that will show you how the display will look for various
dates. Put this in some cell (other than C1) and copy down...

=DATE(YEAR($C$1)-(MONTH($C$1)<ROWS($1:1)),ROWS($1:1),1)

Just type various dates into C1 and watch how the display changes for them.
If you need a different type of display, just let us know.

Rick


"capt" wrote in message
...
Just one more question Rick,
When I reach Dec-08, will Jan-08, at the top of the list, change to Jan-09
or do I have to continue with the list down as far as I wish?
--
capt


"capt" wrote:

Rick,
Fantanstic, it works well.
Happy new year!
--
capt


"Rick Rothstein (MVP - VB)" wrote:

Put this formula...

=DATE(YEAR(NOW())-(MONTH(NOW())<ROWS($1:1)),ROWS($1:1),1)

in the cell where your January date is supposed to be, copy it down
through
the next 11 cells and, finally, apply the Mar-01 date format from the
Format
Cells option to those 12 cells.

Rick


"capt" wrote in message
...
Thanks Gary,
The trouble its changing every date at once. Is there a way in order
that
the year changes when the month actually starts. In other words:
because
we
are in the month of January cell A2 changes to Jan 08 the rest remain
indicating 07 year. Then as we reach the end of Jan and go into
Febuary
then
it changes to Feb 08 and it carrys on changing as we reach that month
for
real.
I hope that makes sense?
--
capt


"Gary''s Student" wrote:

Here is an example of monthly updating:

In A1 enter:
39083

In A2 enter:
=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)) and copy this formula down as
far as
you like.

Select column A and:

Format Cells... Number Custom mmm-yy
--
Gary''s Student - gsnu200762


"capt" wrote:

I have the following table:

Jan-08
Feb-07
Mar-07
Apr-07
May-07
Jun-07
Jul-07
Aug-07
Sep-07
Oct-07
Nov-07
Dec-07

Is there a formula that when we enter a new month, in this case
Feb 07
it
will change to Feb 08 and as we enter the following month in turn
it up
dates
to 08 each time?
--
capt

Sorry Rick,
Iv encounted another problem. When added the formula it has slowed down the
calculation of the workbook.
Is that because the formula has slowed it down or is it something else?
--
capt


"capt" wrote:

No thats fine Rick, your first code will do nicely.
Thank very much.
--
capt


"Rick Rothstein (MVP - VB)" wrote:

No, it will not. When the date is January 1, 2009 is when the Jan-08 will
change. Were you after some other functionality?

Here is a formula that will show you how the display will look for various
dates. Put this in some cell (other than C1) and copy down...

=DATE(YEAR($C$1)-(MONTH($C$1)<ROWS($1:1)),ROWS($1:1),1)

Just type various dates into C1 and watch how the display changes for them.
If you need a different type of display, just let us know.

Rick


"capt" wrote in message
...
Just one more question Rick,
When I reach Dec-08, will Jan-08, at the top of the list, change to Jan-09
or do I have to continue with the list down as far as I wish?
--
capt


"capt" wrote:

Rick,
Fantanstic, it works well.
Happy new year!
--
capt


"Rick Rothstein (MVP - VB)" wrote:

Put this formula...

=DATE(YEAR(NOW())-(MONTH(NOW())<ROWS($1:1)),ROWS($1:1),1)

in the cell where your January date is supposed to be, copy it down
through
the next 11 cells and, finally, apply the Mar-01 date format from the
Format
Cells option to those 12 cells.

Rick


"capt" wrote in message
...
Thanks Gary,
The trouble its changing every date at once. Is there a way in order
that
the year changes when the month actually starts. In other words:
because
we
are in the month of January cell A2 changes to Jan 08 the rest remain
indicating 07 year. Then as we reach the end of Jan and go into
Febuary
then
it changes to Feb 08 and it carrys on changing as we reach that month
for
real.
I hope that makes sense?
--
capt


"Gary''s Student" wrote:

Here is an example of monthly updating:

In A1 enter:
39083

In A2 enter:
=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)) and copy this formula down as
far as
you like.

Select column A and:

Format Cells... Number Custom mmm-yy
--
Gary''s Student - gsnu200762


"capt" wrote:

I have the following table:

Jan-08
Feb-07
Mar-07
Apr-07
May-07
Jun-07
Jul-07
Aug-07
Sep-07
Oct-07
Nov-07
Dec-07

Is there a formula that when we enter a new month, in this case
Feb 07
it
will change to Feb 08 and as we enter the following month in turn
it up
dates
to 08 each time?
--
capt

Well, if you have done nothing else but add the formulas, then I'd have to
suspect them as being the culprit. More than likely, it is the YEAR, MONTH
and NOW functions that are causing the problem. These are "volatile"
functions and, as such, are recalculated **every** time any calculation in
the worksheet is performed. I did a Google search on the words excel,
volatile and functions and came up with three-quarters of a million hits for
it (in case you want to research the volatile function issue on your own).

In the formula I posted, there are 3 volatile function calls; but, with NOW
being embedded within the other two, that may be multiplying the effect.
Now, I am not an "Excel person" as such (my expertise, if you want to call
it that, is in the compiled Visual Basic world), so I don't know this for
sure, but with only 12 uses of my function, I find it hard to believe their
volatile nature could impact the performance of your spreadsheet in any
dramatic way. However, since you are seeing that effect, I guess I must be
wrong in that conclusion. There is a way to minimize the problem though...
reduce the number of direct volatile function calls. With my formulas, you
could do something like this. In an out of the way location (hide the column
afterwards if you don't want anyone seeing it), say Column Z, put =TODAY()
in Z1, =YEAR(Z1) in Z2 and =MONTH(Z1) in Z3. Then change my formula to
this...

=DATE($Z$2-($Z$3<ROWS($1:1)),ROWS($1:1),1)

and copy that down. This will reduce the number of volatile function calls
within those 12 rows significantly and may help in your overall performance
problem (or, at least, it will minimize the impact of my formulas on the
problem). If you have any other references to NOW or NOW embedded in the
YEAR or MONTH function in your spreadsheet, you can point them to these
Z-column cells as well (remember to use absolute references if you are
copying them down).

Oh, by the way, in researching the volatile functions, I once came across
this advice... if you had volatile function calls and removed them, it might
be necessary to rebuild the dependency tree in order to make Excel "forget"
about the volatile functions. You can do this by pressing Ctrl+Shift+Alt+F9
after you have finished editing out the volatile functions.

Rick



"capt" wrote in message
...
Sorry Rick,
Iv encounted another problem. When added the formula it has slowed down
the
calculation of the workbook.
Is that because the formula has slowed it down or is it something else?
--
capt


"capt" wrote:

No thats fine Rick, your first code will do nicely.
Thank very much.
--
capt


"Rick Rothstein (MVP - VB)" wrote:

No, it will not. When the date is January 1, 2009 is when the Jan-08
will
change. Were you after some other functionality?

Here is a formula that will show you how the display will look for
various
dates. Put this in some cell (other than C1) and copy down...

=DATE(YEAR($C$1)-(MONTH($C$1)<ROWS($1:1)),ROWS($1:1),1)

Just type various dates into C1 and watch how the display changes for
them.
If you need a different type of display, just let us know.

Rick


"capt" wrote in message
...
Just one more question Rick,
When I reach Dec-08, will Jan-08, at the top of the list, change to
Jan-09
or do I have to continue with the list down as far as I wish?
--
capt


"capt" wrote:

Rick,
Fantanstic, it works well.
Happy new year!
--
capt


"Rick Rothstein (MVP - VB)" wrote:

Put this formula...

=DATE(YEAR(NOW())-(MONTH(NOW())<ROWS($1:1)),ROWS($1:1),1)

in the cell where your January date is supposed to be, copy it
down
through
the next 11 cells and, finally, apply the Mar-01 date format from
the
Format
Cells option to those 12 cells.

Rick


"capt" wrote in message
...
Thanks Gary,
The trouble its changing every date at once. Is there a way in
order
that
the year changes when the month actually starts. In other words:
because
we
are in the month of January cell A2 changes to Jan 08 the rest
remain
indicating 07 year. Then as we reach the end of Jan and go into
Febuary
then
it changes to Feb 08 and it carrys on changing as we reach that
month
for
real.
I hope that makes sense?
--
capt


"Gary''s Student" wrote:

Here is an example of monthly updating:

In A1 enter:
39083

In A2 enter:
=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)) and copy this formula down
as
far as
you like.

Select column A and:

Format Cells... Number Custom mmm-yy
--
Gary''s Student - gsnu200762


"capt" wrote:

I have the following table:

Jan-08
Feb-07
Mar-07
Apr-07
May-07
Jun-07
Jul-07
Aug-07
Sep-07
Oct-07
Nov-07
Dec-07

Is there a formula that when we enter a new month, in this
case
Feb 07
it
will change to Feb 08 and as we enter the following month in
turn
it up
dates
to 08 each time?
--
capt

Thanks Rick,
Iv done as you have explained. It has speeded the process up but still abit
slow. I expect I have,as you mentioned, somewhere some voatile functions. I
will have to investigate further.
Many thanks for your help and patience.
--
capt


"Rick Rothstein (MVP - VB)" wrote:

Well, if you have done nothing else but add the formulas, then I'd have to
suspect them as being the culprit. More than likely, it is the YEAR, MONTH
and NOW functions that are causing the problem. These are "volatile"
functions and, as such, are recalculated **every** time any calculation in
the worksheet is performed. I did a Google search on the words excel,
volatile and functions and came up with three-quarters of a million hits for
it (in case you want to research the volatile function issue on your own).

In the formula I posted, there are 3 volatile function calls; but, with NOW
being embedded within the other two, that may be multiplying the effect.
Now, I am not an "Excel person" as such (my expertise, if you want to call
it that, is in the compiled Visual Basic world), so I don't know this for
sure, but with only 12 uses of my function, I find it hard to believe their
volatile nature could impact the performance of your spreadsheet in any
dramatic way. However, since you are seeing that effect, I guess I must be
wrong in that conclusion. There is a way to minimize the problem though...
reduce the number of direct volatile function calls. With my formulas, you
could do something like this. In an out of the way location (hide the column
afterwards if you don't want anyone seeing it), say Column Z, put =TODAY()
in Z1, =YEAR(Z1) in Z2 and =MONTH(Z1) in Z3. Then change my formula to
this...

=DATE($Z$2-($Z$3<ROWS($1:1)),ROWS($1:1),1)

and copy that down. This will reduce the number of volatile function calls
within those 12 rows significantly and may help in your overall performance
problem (or, at least, it will minimize the impact of my formulas on the
problem). If you have any other references to NOW or NOW embedded in the
YEAR or MONTH function in your spreadsheet, you can point them to these
Z-column cells as well (remember to use absolute references if you are
copying them down).

Oh, by the way, in researching the volatile functions, I once came across
this advice... if you had volatile function calls and removed them, it might
be necessary to rebuild the dependency tree in order to make Excel "forget"
about the volatile functions. You can do this by pressing Ctrl+Shift+Alt+F9
after you have finished editing out the volatile functions.

Rick



"capt" wrote in message
...
Sorry Rick,
Iv encounted another problem. When added the formula it has slowed down
the
calculation of the workbook.
Is that because the formula has slowed it down or is it something else?
--
capt


"capt" wrote:

No thats fine Rick, your first code will do nicely.
Thank very much.
--
capt


"Rick Rothstein (MVP - VB)" wrote:

No, it will not. When the date is January 1, 2009 is when the Jan-08
will
change. Were you after some other functionality?

Here is a formula that will show you how the display will look for
various
dates. Put this in some cell (other than C1) and copy down...

=DATE(YEAR($C$1)-(MONTH($C$1)<ROWS($1:1)),ROWS($1:1),1)

Just type various dates into C1 and watch how the display changes for
them.
If you need a different type of display, just let us know.

Rick


"capt" wrote in message
...
Just one more question Rick,
When I reach Dec-08, will Jan-08, at the top of the list, change to
Jan-09
or do I have to continue with the list down as far as I wish?
--
capt


"capt" wrote:

Rick,
Fantanstic, it works well.
Happy new year!
--
capt


"Rick Rothstein (MVP - VB)" wrote:

Put this formula...

=DATE(YEAR(NOW())-(MONTH(NOW())<ROWS($1:1)),ROWS($1:1),1)

in the cell where your January date is supposed to be, copy it
down
through
the next 11 cells and, finally, apply the Mar-01 date format from
the
Format
Cells option to those 12 cells.

Rick


"capt" wrote in message
...
Thanks Gary,
The trouble its changing every date at once. Is there a way in
order
that
the year changes when the month actually starts. In other words:
because
we
are in the month of January cell A2 changes to Jan 08 the rest
remain
indicating 07 year. Then as we reach the end of Jan and go into
Febuary
then
it changes to Feb 08 and it carrys on changing as we reach that
month
for
real.
I hope that makes sense?
--
capt


"Gary''s Student" wrote:

Here is an example of monthly updating:

In A1 enter:
39083

In A2 enter:
=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)) and copy this formula down
as
far as
you like.

Select column A and:

Format Cells... Number Custom mmm-yy
--
Gary''s Student - gsnu200762


"capt" wrote:

I have the following table:

Jan-08
Feb-07
Mar-07
Apr-07
May-07
Jun-07
Jul-07
Aug-07
Sep-07
Oct-07
Nov-07
Dec-07

Is there a formula that when we enter a new month, in this
case
Feb 07
it
will change to Feb 08 and as we enter the following month in
turn
it up
dates
to 08 each time?
--
capt