Calendar Array-formula
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
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. |
All times are GMT +1. The time now is 02:02 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com