Microsoft Office Tutorials and References
In Depth Information
Tip 40: Displaying a Live Calendar in a Range
Displaying a Live Calendar in a Range
This tip describes how to create a “live” calendar in a range of cells. Figure 40-1 shows an example. If
you change the date that’s displayed at the top of the calendar, the calendar recalculates to display
the dates for the month and year.
Figure 40-1: This calendar was created with a complex array formula.
To create this calendar in the range B2:H9, follow these steps:
1. Select B2:H2 and then merge the cells by choosing Home Alignment Merge & Center.
2. Enter a date into the merged range.
The day of the month isn’t important, so change the format of the cell to a custom format
that doesn’t display the day: mmmm, yyyy.
3. Enter the abbreviated day names in the range B3:H3.
4. Select B4:H9 and then enter the following array formula without the line breaks.
Note: 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),MONTH(B2),1))-1)
+{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1)
Search JabSto ::




Custom Search