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" wrote in message
...
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.