Microsoft Office Tutorials and References
In Depth Information
4. Select B4:H9 and enter this array formula.
Remember, to enter an array formula, use 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),MONTH(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 in the Format
Cells dialog box to specify this custom number format.
6. Adjust the column widths and format the cells as you like.
Change the month and year in cell B2, and the calendar will update automatically. After creating this calendar,
you can copy the range to any other worksheet or workbook.
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.
See Chapter 14 for more information about array constants.
The array formula can be simplified quite a bit by removing the IF function, which checks to make sure that the
date is in the specified 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
This version of the formula displays the days from the preceding month and the next month.
Figure 15-17 shows 12 instances of the array formula calendar, for an entire year.
Search JabSto ::




Custom Search