Calendar 1st month ok but 2nd to 12th how.

[Steved]

Hello from Steved

Below I got from this forum

Ok I done the below but also create 11 extra months.
My calendar is 4 across by 3 down giving me 12months.
Ok all 12 calendars has the same month on it, how do
I change the next month to february and all the others until
I have December please.Thanks

oh yes one more thing I've changed NOW() to A1 being the first day off the
new year 01/01/06.

=IF(MONTH(DATE(YEAR(NOW()),MONTH(NOW()),1))-
MONTH(DATE(YEAR(NOW()),MONTH(NOW()),1)-
(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1))-1)+
{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1),"",DATE(YEAR(NOW()),
MONTH(NOW()),1)-(WEEKDAY(DATE(YEAR(NOW()),
MONTH(NOW()),1))-1)+{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1)


To use it:

1. Copy the formula text to the clipboard
2. Activate a sheet and select a 7-col by 6-row range
3. Press F2
4. Press Ctrl+V to paste the formula into the active cell
5. Press Ctrl+Shift+Enter (to make it a multicell array formula)
6. Format the cells using the "d" number format.

 

[Max]

Not sure if you're implementing it correctly, Steved ..
Had a play with the "calendar" array formula you found
and created a demo file with it (the formula's fantastic!)

Here's a link: http://cjoint.com/?jCjnufCfrH
WorksheetCalendar.xls

and here's what I did ..

In Sheet1
-----------
Created a DV droplist in say, F1, via:
Data > Validation
Allow: List
Source range: =MthYr
where MthYr is a named range in another sheet: DV
housing the 1st of month dates for 3 years (2005 - 2007)

The DV in F1 will allow us to select the desired month-year for the calendar
to be set-up in B3:H9

Placed "day" labels into B3:H3: Sun, Mon, ... Sat

Selected a 7C x 6R range, i.e. B4:H9

Placed in the formula bar
and array-entered with CTRL+SHIFT+ENTER:

=IF(MONTH(DATE(YEAR(F1),MONTH(F1),1))-MONTH(DATE(YEAR(F1),
MONTH(F1),1)-(WEEKDAY(DATE(YEAR(F1),MONTH(F1),1))-1)+
{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1),"",
DATE(YEAR(F1),MONTH(F1),1)-(WEEKDAY(DATE(YEAR(F1),
MONTH(F1),1))-1)+{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1)

(The amended array formula points to the DV cell in F1)

Sheet1 with the calendar created can then be copied as desired

 

[Max]

Another link: http://www.savefile.com/files/4349494
WorksheetCalendar.xls

 

[Roger Govier]

Hi Max

I was playing about with this for another poster in this group earlier in
the month (20/09). He also wanted to make the calendar start on a Monday,
rather than a Sunday.

I amended the 2 occurrences of
(WEEKDAY(DATE(YEAR(F1),MONTH(F1),1))-1)
to
(WEEKDAY(DATE(YEAR(F1),MONTH(F1),1))-2)
which makes it start on a Monday instead.

However, it works well for months other than May-05, where the 1st is a
Sunday, and only shows dates from 2nd through 31st. I guess the same applies
to any month beginning on a Sunday, but I have spent some time trying to
figure out how to amend the formula to correct this, but I have failed
miserably.

Perhaps you can see the way.


Regards

Roger Govier

 

[Max]

.. Perhaps you can see the way

No, I'm afraid not <g> .. , but I did try-out a cheat
(reading Bernie's closing lines in: http://tinyurl.com/dvskz )
which seems to work ok in Sheet2 in the attached revised file:
WorksheetCalendar_2.xls
re- links at either:

http://cjoint.com/?jCk6l8Wz3z
http://www.savefile.com/files/2959578

Sheet2 has the same array formula as in Sheet1,
but with a cheat attempt <g> to show calendar starting with Monday

An additional col I is created to link to the hidden col B,
with a "pull up" of numbers into col I

Placed in I4:
=IF($B$4<>"",OFFSET($B$4,ROWS($A$1:A1)-1,),OFFSET($B$4,ROWS($A$1:A1),))

I4 is copied down to I8

 

[Roger Govier]

Hi Max

I hadn't seen Bernie's posting.
Cheating..? No, just bending to fit the OP's requirements.
Knowing that neither you nor Bernie could solve it directly makes me feel a
whole heap better somehow!!!!

Thanks for the response.

Regards

Roger Govier

 

[Max]

You're welcome, Roger !
Cheers

 

[Steved]

Hello Max from Steved

Max I used your formula below And have got 4 by 3 calendar = 12 months

=DATE(YEAR(L1),MONTH(L1)+1,DAY(L1))

I've used the above for the Month Titles

The objective is it to fit on a A4 Sheet Landscape and in Cell A1 i've got
01/01/2006
Now all I do is change the date in A1 and bingo it updates

I thankyou very much for your formula.

 

[Max]

Glad you sorted out out whatever it was that you wanted, Steved !