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

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

Post a comment