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.