Month question?

[capt]

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?

 

[Gary''s Student]

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

 

[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?

 

[Rick Rothstein \(MVP - VB\)]

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]

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

 

[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?

 

[Rick Rothstein \(MVP - VB\)]

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]

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

 

[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

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

 

[Rick Rothstein \(MVP - VB\)]

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]

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.