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)

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.

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

I too have both array and formula separators as ";". Usually array formulas
work for me, unless I'm trying to use 2D array (which is not the case
here) - like VLOOKUP(value,{val11,val12;val21,val22;...},2,0) - try to
replace all commas with semicolons :-))
But this formula somehow behaves differently with my regional settings.


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


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

My argument separator is ";"

Would it be possible to mail me a wbk. Maybe it sorts out that way...
Sige

Sige,

Sorry to jump in so late, but here is a NON-Array, non-array using formula that will generate the
current month's calendar.

Select a 7 row by 7 column block of cells, let's say A1:G7, press F2, paste in this formula:

=DATE(YEAR(NOW()),MONTH(NOW()),1)-WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1))+1+(ROW()-ROW($A$2))*7+(COLUMN()-COLUMN($A$2))

and press Ctrl-Enter (NOT Ctrl-Shift-Enter, just Ctrl-Enter, to enter the formula in all 49 cells)

Then format the first row for "ddd", and the next 6 rows for "d". A nice touch is to use
Conditional formatting on A2:G7 with the formula:

=MONTH(A2)=MONTH($A$3)

Change the background and font color, and the current month will be highlighted. Other formatting
and CF will make the calendar even prettier. I have a version tied to two spin buttons that I use to
see previous or future months, so if you would like a copy of that, I can email it to you privately.

HTH,
Bernie
MS Excel MVP


wrote in message
ups.com...
My argument separator is ";"

Would it be possible to mail me a wbk. Maybe it sorts out that way...
Sige

Hi Sige

I have emailed you directly with a copy of the test file I set up.
When I looked at it again, I found that the error I had created wasn't the
same as you described.

In my case, I have a column of Monday 5, a column of Tuesday 6 etc. as
opposed to the rows you described.

I then further amended the formula and the second sets of arrays I did the
opposite and changed "," to ";"
{0,1,2,3,4,5}*7+{1;2;3;4;5;6;7}-1)

I then pasted these to a 6 column x 7 row matrix (as opposed to the original
7 x 6) and the whole thing worked, except the dates incremented down the
column, then back to the top of the next column etc.

I guess it must somehow be due to the differences in the separator. I hope
the file I sent helps you to figure it out.

I have to say that Arvi's non-array formula works fine for me also.

Regards

Roger Govier


wrote:
My argument separator is ";"

Would it be possible to mail me a wbk. Maybe it sorts out that way...
Sige

Hi There,

Roger was so kind to mail me his solution(s) ... the original formula
looks as follows on my system.

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

Works like a charm!
I will just try to get monday as first day of the week.

Hi Bernie,
Thanks a lot for your non-array solution!

Looking forward to see the months spinning :o)

Sige

Hi Sige

Glad you got it to work.
With regard to making the first column be a Monday just change the -1 to a
-2 in both parts of the formula
(WEEKDAY(DATE(YEAR(A1),MONTH(A1),1))-2)

You also menioned in your private email to me, making the formula work for
other months.
One way would be to change the NOW() to a cell reference like A1 and mark a
block of cells from B2:G6 to paste the following formula (English version,
change separators as before)

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

If you format cell A1 as mmmm then it will nicely display the month name at
the top of your calendar.

The other way of getting different months would be to still use the NOW()
function, but for each occurrence of MONTH(NOW()), make it MONTH(NOW())+1 or
+2 or -1, -2 etc.

Regards

Roger Govier


wrote:
Hi There,

Roger was so kind to mail me his solution(s) ... the original formula
looks as follows on my system.

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

Works like a charm!
I will just try to get monday as first day of the week.

Sige,

Sent to the HotMail address.

HTH,
Bernie
MS Excel MVP


wrote in message
oups.com...
Hi Bernie,
Thanks a lot for your non-array solution!

Looking forward to see the months spinning :o)

Sige

Make sure you don't change the -1 in {1,2,3,4,5,6,7}-1

--
HTH

Bob Phillips

"Roger Govier" wrote in message
...
Hi Sige

Glad you got it to work.
With regard to making the first column be a Monday just change the -1 to a
-2 in both parts of the formula
(WEEKDAY(DATE(YEAR(A1),MONTH(A1),1))-2)

You also menioned in your private email to me, making the formula work for
other months.
One way would be to change the NOW() to a cell reference like A1 and mark
a
block of cells from B2:G6 to paste the following formula (English version,
change separators as before)

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

If you format cell A1 as mmmm then it will nicely display the month name
at
the top of your calendar.

The other way of getting different months would be to still use the NOW()
function, but for each occurrence of MONTH(NOW()), make it MONTH(NOW())+1
or
+2 or -1, -2 etc.

Regards

Roger Govier


wrote:
Hi There,

Roger was so kind to mail me his solution(s) ... the original formula
looks as follows on my system.

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

Works like a charm!
I will just try to get monday as first day of the week.

This code was realy great!

This may be interesting for people ho needs a generic calendar for a
given year:
I've earlier made a year calendar with weeknumbers at
http://www.pvv.org/~nsaa/excel.html#21 (Excel_Calendar.xls). The
Calendar follow the ISO standard (ISO 8601). Commonly adopted in
Europe.