Excel 2010 Bible

Chapter 17: Performing Magic with Array Formulas

399

4. Select B4:H9 and enter this array formula. Remember: To enter an array formula, press
   Ctrl+Shift+Enter (not just Enter).
   {=IF(MONTH(DATE(YEAR(B2),MONTH(B2),1))<>MONTH(DATE(YEAR(B2),
   MONTH(B2),1)-(WEEKDAY(DATE(YEAR(B2),MONTH(B2),1))-1)+
   {0;1;2;3;4;5}7+{1,2,3,4,5,6,7}-1),””,
   DATE(YEAR(B2),MONTH(B2),1)-(WEEKDAY(DATE(YEAR(B2),MONT
   H(B2),1))-1)+
   {0;1;2;3;4;5}7+{1,2,3,4,5,6,7}-1)}


5. Format the range B4:H9 to use this custom number format: d. This step formats the
   dates to show only the day. Use the Custom category in the Number tab of the Format
   Cells dialog box to specify this custom number format.


6. Adjust the column widths and format the cells as you like.


7. Change the month and year in cell B2. The calendar updates automatically.

After creating this calendar, you can copy the range to any other worksheet or workbook.

FIGURE 17.13

Displaying a calendar by using a single array formula.

The array formula actually returns date values, but the cells are formatted to display only the day

portion of the date. Also, notice that the array formula uses array constants.

Cross-Reference
See Chapter 16 for more information about array constants. n