Calendar array formula

claudehollett · Nov 1, 2005

Walkenbach lists an array formula for an updating calendar in Excel 2003 Formulas on page 163. This formula works very well for a calendar to look at dates but I need a calendar in which data and events can be entered. I use a small header row over each week to contain the dates and a larger row under the date to use for data and events. As he says, I named a cell "m" for Month and "y" for Year. Can anyone modify the formula to work to suppy dates on every other row of the calendar or is this not possible with arrays?

Here is the formula:

{=IF(MONTH(DATE(y,m,1))<>MONTH(DATE(y,m,1)-(WEEKDAY(DATE(y,m,1))-1)+{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1),"",DATE(y,m,1)-(WEEKDAY(DATE(y,m,1))-1)+{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1)}

Frankly, this formula is over my head. If anyone can help I would appreciate it. Thanks, Claude

PA HS Teacher · Nov 13, 2005

Do you mean like this?

Code:

=IF({0;1;0;1;0;1;0;1;0;1;0;1},"",
IF(MONTH(DATE(y,m,1))<>MONTH(DATE(y,m,1)-(WEEKDAY(DATE(y,m,1))-1)+{0;0;1;0;2;0;3;0;4;0;5;0}*7+{1,2,3,4,5,6,7}-1),"",
                                                      DATE(y,m,1)-(WEEKDAY(DATE(y,m,1))-1)+{0;0;1;0;2;0;3;0;4;0;5;0}*7+{1,2,3,4,5,6,7}-1))

Confirmed with Control + Shift + Enter

It will not allow Anything to be entered in those blank rows, because they contain part of the array formula. Perhaps you'd be willing to paste special as values before entering data?

[edit] Although, the JW array formula, can be modified into a regular formula, allowing for data entry on the blank rows.

in A1 and Pasted across and down.

Code:

=IF(MOD((ROW(A2)-ROW(A$1)-1)/2,1)<>0,"",
IF(MONTH(DATE(y,m,1))<>MONTH(DATE(y,m,1)-(WEEKDAY(DATE(y,m,1))-1)+(ROW(A2)-ROW(A$1)-1)*7/2+COLUMN(B1)-COLUMN($A1)-1),"",
                                                      DATE(y,m,1)-(WEEKDAY(DATE(y,m,1))-1)+(ROW(A2)-ROW(A$1)-1)*7/2+COLUMN(B1)-COLUMN($A1)-1))

Book1