This superb Array formula deserves all the merits from J-Walk.
Found on Dick Kusleika's site (http://www.dicks-blog.com/)

***
Despite the step-by step description I do not manage to get the formula
to work!
***

Here it is:
This formula isn't very long, and it's really not all that ugly.
But it's one of my favorite formulas:


=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.

Voila! You have a calendar for the current month.

So, are you applauding it, or asking for help to get it to work?

--
HTH

Bob Phillips

wrote in message
oups.com...
This superb Array formula deserves all the merits from J-Walk.
Found on Dick Kusleika's site (http://www.dicks-blog.com/)

***
Despite the step-by step description I do not manage to get the formula
to work!
***

Here it is:
This formula isn't very long, and it's really not all that ugly.
But it's one of my favorite formulas:


=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.

Voila! You have a calendar for the current month.

At the moment asking help to get it work... ;o)
(I followed the steps a couple of times.)

But I am sure i will applaude it later on! :o)

I followed the instructions and it worked fine.

The crucial part is after selecting the 7 columns by 6 rows in the
worksheet, hit the F2 key which will take you into formula edit, and copy
the formula in, and then use Ctrl-Shift-Enter to commit it.

--
HTH

Bob Phillips

wrote in message
ps.com...
At the moment asking help to get it work... ;o)
(I followed the steps a couple of times.)

But I am sure i will applaude it later on! :o)

It bugs on me ...

For those with ;-separator instead of ,-separator:

=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)

But on my first and last row I do not get anything.
On row 2 I got all mondays (all the same), row3: all the same
tuesdays, row4: wedns, row5: thursds
like:
....
ma 05-sep-05 ma 05-sep-05 ma 05-sep-05 ...
di 13-sep-05 di 13-sep-05 di 13-sep-05 ...
....
in 7 columns though.

Strange, strange, strange...

Hi

Like Bob, I have no problem with the original formula.
However, where you have changed "," for ";" because of your language
separator, you need to use a different separator within the array functions
{0;1;2;3;4;5} as there is a difference in the way an array is treated with
different separators.

If I change the separator from ";" to "," with my UK settings, I can
re-create your problem.

I don't know what the correct separator should be for your language, maybe
you need "," rather than ";"????

Regards

Roger Govier


wrote:
It bugs on me ...

For those with ;-separator instead of ,-separator:

=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)

But on my first and last row I do not get anything.
On row 2 I got all mondays (all the same), row3: all the same
tuesdays, row4: wedns, row5: thursds
like:
....
ma 05-sep-05 ma 05-sep-05 ma 05-sep-05 ...
di 13-sep-05 di 13-sep-05 di 13-sep-05 ...
....
in 7 columns though.

Strange, strange, strange...

Hi


Doesn't work properly for me either - it returns the same day for entire
week.

Try instead this (created on fly, but it's working, I checked) non-array
formula (started from cell B2, monday as 1st day of week)

=IF(MONTH(DATE(YEAR(TODAY()),MONTH(TODAY()),1)-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1))+(COL UMN()-1)+(ROW()-2)*7)=MONTH(TODAY()),DATE(YEAR(TODAY()),MONTH(TODA Y()),1)-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1))+(COL UMN()-1)+(ROW()-2)*7,"")


--
Arvi Laanemets
( My real mail address: arvil<attarkon.ee )


wrote in message
oups.com...
This superb Array formula deserves all the merits from J-Walk.
Found on Dick Kusleika's site (http://www.dicks-blog.com/)

***
Despite the step-by step description I do not manage to get the formula
to work!
***

Here it is:
This formula isn't very long, and it's really not all that ugly.
But it's one of my favorite formulas:


=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.

Voila! You have a calendar for the current month.

That is most odd, it works fine for me. It should be 6 weeks!

--
HTH

Bob Phillips

"Arvi Laanemets" wrote in message
...
Hi


Doesn't work properly for me either - it returns the same day for entire
week.

Try instead this (created on fly, but it's working, I checked) non-array
formula (started from cell B2, monday as 1st day of week)


=IF(MONTH(DATE(YEAR(TODAY()),MONTH(TODAY()),1)-WEEKDAY(DATE(YEAR(TODAY()),MO
NTH(TODAY()),1))+(COLUMN()-1)+(ROW()-2)*7)=MONTH(TODAY()),DATE(YEAR(TODAY())
,MONTH(TODAY()),1)-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1))+(COL UMN()-1
)+(ROW()-2)*7,"")


--
Arvi Laanemets
( My real mail address: arvil<attarkon.ee )


wrote in message
oups.com...
This superb Array formula deserves all the merits from J-Walk.
Found on Dick Kusleika's site (http://www.dicks-blog.com/)

***
Despite the step-by step description I do not manage to get the formula
to work!
***

Here it is:
This formula isn't very long, and it's really not all that ugly.
But it's one of my favorite formulas:


=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.

Voila! You have a calendar for the current month.