Calendar Array-formula

S

SIGE_GOEVAERTS

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

SIGE_GOEVAERTS

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! :eek:)
 
A

Arvi Laanemets

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))+(COLUMN()-1)+(ROW()-2)*7)=MONTH(TODAY()),DATE(YEAR(TODAY()),MONTH(TODAY()),1)-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1))+(COLUMN()-1)+(ROW()-2)*7,"")
 
B

Bob Phillips

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

SIGE_GOEVAERTS

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

Roger Govier

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
 
A

Arvi Laanemets

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

SIGE_GOEVAERTS

My argument separator is ";"

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

Bernie Deitrick

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
 
R

Roger Govier

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
 
S

SIGE_GOEVAERTS

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

SIGE_GOEVAERTS

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

Looking forward to see the months spinning :eek:)

Sige
 
R

Roger Govier

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
 
S

sandved

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Find next Sunday 7
Use array/braces **WITHIN** a formula? 6
Calendar 1st month ok but 2nd to 12th how. 8
Search closest day in the past from today 2
Array Formula Calendar 6
Calendar Logic: Recurring Dates 3
calendar to 12 month calendar 11
Mapping strings to integers 6

Top