Calender Generation

[Joel]

Hi I was wondering if you could please help

I have made a calender in Excel for this year 2008 on sheet 1 it just shows
a table devided into 12 months with the relevent dates

My Question is can I get it to generate a calender 2009 with the with the
correct date on automatically on sheet 2 and so on

Thanks Joel

 

[JLatham]

Joel,
The answer to this one is filled with a lot of "if"s , "but"s and "maybe"s.
Basically the answer is yes, but how easy it would be to do depends much on
your current calendar layout. John Walkenbach has a massive array formula
published in some of his books on Excel that will automatically provide a
calendar for any month/year combination. The 'problem' with it is that the
cells are occupied by the formula/date and you can't type notes into the
cells - but for printing a calendar for desk or wall, it's great.

For setting up calendars to have a date on one row with note area
underneath, things get a bit more complicated, but still do-able. Mostly
this would all be handled by VBA code (if I did it - as I have in the past).

It's almost easier to do it manually - start with a calendar for January and
enter the date for January 1st manually, then in the rest of the month's
cells you put a formula that adds 1 day to the previous day's date for each
day of the month. For Feb thru Dec, you go to the proper day of the week in
that month's calendar area and add 1 day to the last day of the previous
month, then continue with the add 1 day formula for the rest of it. As I
said, this can also be done with VBA code or with an array formula such as
Walkenbach has published.

 

[Joel]

Thank you very mouch for your very detailed reply it is most appreciiated not
many people bother replying like that anymore. It is for general interest
only so I might take a look at his book if I can find it

Thanks again Joel

 

[JP]

I think this is what you meant.

http://j-walk.com/ss/excel/files/calarray.exe (from
http://j-walk.com/ss/excel/files/general.htm)


HTH,
JP

 

[JLatham]

JP - good deal, that's probably exactly what I was thinking of (I didn't go
so far as to actually open it). Good to know that JW has published it for
general consumption - I was hesitant to try to copy it from the book or CD
(copyright rules and common courtesy and all like that, you know?).

Joel: If that formula is the one I think it is, it's a doozy! It is an
'array' formula which means that you choose all of the cells you want it to
be in first, then copy and paste the formula into the formula bar, and then
you commit the formula by using the [Ctrl]+[Shift]+[Enter] keys (all pressed
at once) instead of just the [Enter] key. Once you do that the formula will
show up within curley braces: {}

If you ever have to edit an array formula, you must recommit it with the
same [Ctrl]+[Shift]+[Enter] keypress to keep it as an array formula.

 

[JP]

Eh, I just use one from MSFT:
http://tinyurl.com/3eydbj


Thx,
JP

JP - good deal, that's probably exactly what I was thinking of (I didn't go
so far as to actually open it).  Good to know that JW has published it for
general consumption - I was hesitant to try to copy it from the book or CD
(copyright rules and common courtesy and all like that, you know?).

Joel: If that formula is the one I think it is, it's a doozy!  It is an
'array' formula which means that you choose all of the cells you want it to
be in first, then copy and paste the formula into the formula bar, and then
you commit the formula by using the [Ctrl]+[Shift]+[Enter] keys (all pressed
at once) instead of just the [Enter] key.  Once you do that the formula will
show up within curley braces: {}

If you ever have to edit an array formula, you must recommit it with the
same [Ctrl]+[Shift]+[Enter] keypress to keep it as an array formula.

I think this is what you meant.

- Show quoted text -