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
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Steved" <[email protected]> wrote in message
news:[email protected]...
> 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]

Another link: http://www.savefile.com/files/4349494
WorksheetCalendar.xls
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

[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 wrote:
> 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
> --
> Rgds
> Max
> xl 97
> ---
> Singapore, GMT+8
> xdemechanik
> http://savefile.com/projects/236895
> --
> "Steved" <[email protected]> wrote in message
> news:[email protected]...
>
>>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]

> .. 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
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

[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 wrote:
>>.. 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
> --
> Rgds
> Max
> xl 97
> ---
> Singapore, GMT+8
> xdemechanik
> http://savefile.com/projects/236895
> --
>
>

[Max]

You're welcome, Roger !
Cheers
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

[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" wrote:

> 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
> --
> Rgds
> Max
> xl 97
> ---
> Singapore, GMT+8
> xdemechanik
> http://savefile.com/projects/236895
> --
> "Steved" <[email protected]> wrote in message
> news:[email protected]...
> > 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]

Glad you sorted out out whatever it was that you wanted, Steved !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--