Microsoft Office Tutorials and References
In Depth Information
Displaying a Calendar in a Range
Displaying a Calendar in a Range
This tip describes how to create a “live” calendar in a range of cells. Figure 110-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 110-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.
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),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, which displays only the day: d.
6. Adjust the column widths, and format the cells the way you like.
Change the date and year, and the calendar updates automatically. After creating this calendar,
you can copy the range to any other worksheet or workbook.
 
Search JabSto ::




Custom Search