# Calendar array formula

#### claudehollett

##### Board Regular
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

### Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
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?

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

Replies
2
Views
94
Replies
6
Views
72
Replies
9
Views
156
Replies
2
Views
302
Replies
3
Views
340

1,206,969
Messages
6,075,918
Members
446,168
Latest member
listeninglilly

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back