## An array formula calendar

Figure 3-7 shows a worksheet set up to display a calendar for any month (change the month, and the calendar updates). Believe it or not, the calendar is created with a single array formula that occupies 42 cells.

 A 3 April, 2007 4 Vin Man fur Wnd tint M K9 5 1 2 3 * 6 7 6 \$ ? 11 1J 7 15 16- 17 1» IS 20 ■ti S il i) 14 IS it ÎT It 9 ¡9 30 10 11 12 - H * i * h VJkfïlrLrt . COJ ' . _

Figure 3-7: A single multicell array formula is all it takes to make a calendar for any month in any year.

Figure 3-7: A single multicell array formula is all it takes to make a calendar for any month in any year.

The array formula, entered in the range B5:H10, is:

{=IF(MONTH(DATE(YEAR(B3),MONTH(B3),1))<>MONTH(DATE(YEAR(B3),

MONTH(B3),1)-(WEEKDAY(DATE(YEAR(B3),MONTH(B3),1))-1)

DATE(YEAR(B3),MONTH(B3),1)-(WEEKDAY(DATE(YEAR(B3),

MONTH(B3),1))-1)+{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1)}

CD- The companion CD-ROM contains a workbook with the calendar example, as well as ROM several additional array formula examples. The file is named array formula examples.xlsx.

0 0