How to set up a basic calendar in a spreadsheet. All it takes is a few formulas and a bit of fancy formatting. This month we'll create a calendar in Excel. Next month we'll show how to copy information from a spreadsheet database into your calendar.
Our example was constructed in Microsoft's Excel 97; however, Excel 5 and 95 use the same instructions.
1. Open a new worksheet, double-click the current sheet's tab, and name it Calendar. In cell A1, enter the first day of the month that you want your calendar to display--for example, 7/1/97. Select Format•Cells, and in the Format Cells dialog box, click the Number tab, select Custom in the Category list, and enter mmmm yyyy in the Type edit box. Before closing the dialog box, click the Font tab and choose an appropriately large font for the calendar title, then click OK.
2. Select A1:G1 and click the Center Across Columns (or Align Over Columns) button on the Formatting toolbar.
3. Enter the numbers and the days of the week shown in rows 2 and 3.
4. In cell A4, type in this formula: = if (weekday($A$1)>A2,"",$A$1+A2weekday($A$1)). Use the procedure described above to assign A4 the custom number format d, then copy A4 to B4:G4. This formula calculates the dates for the month, beginning on the appropriate day of the week.
5. In A6, enter =G4+1. In B6, enter =A6+1 and copy it to C6:G6. Then copy A6:G6 to A8:G8 and A10:G10. Don't worry about the number formats yet; we'll set those in a moment.
6. Since the month might end on any day in the last two weeks of the calendar (located in rows 12 and 14), we must create a formula-based range name: Select A1:G10, and choose Insert•Name•Define. Enter LastDay in the 'Names in Workbook' box. In the 'Refers to' box, enter the formula =calendar!$A$1+day(date(year(calendar!$A$1),month(calendar!$A$1),+1,1)1).
7. Select rows 12 and 14 and choose Format•Cell. Make sure the Number tab is selected, then click Custom in the Category list and enter d in the Type edit box.
8. Now, in cell A12, enter the formula =if(A10+7>lastday,"",A10+7), and copy the formula to B12:G12.
9. In cell A14, enter =if(A10+14>lastday,"",A12+7) and copy it to B14:G14.
10. Now let's format the calendar. First, turn off the worksheet's grid lines: Select Tools•Options, make sure that the View tab is selected, uncheck Gridlines in the Windows Options section of the dialog box, and click OK. Next, select columns A through G, select Format•Column•Width, enter 16, and click OK. Select row 4, select Format•Cells, click the Font tab, set the point size to 14, and click OK. Select row 5, choose Format•Cells, click the Alignment tab, set the Vertical Alignment to Top, click the Wrap Text box, and click OK. With row 5 still selected, choose Format•Row•Height, enter 54, and click OK. Select A3:G3, choose Format•Cells, click the Border tab, click Outline, and then click OK. Now click the Borders button on the Formatting toolbar and select a vertical alignment button from the resulting drop-down menu (which is shown here). Repeat the Format•Cells•Border command for A4:G5.
11. Finally, select rows 4 and 5, double-click the Format Painter tool, then in succession select rows 6, 8, 10, 12, and so on to complete the formatting. Click the Format Painter icon again to turn off the tool. As a finishing touch, you may want to hide the numbers in row 2: Select that row, then choose Format•Row•Hide.
12. Save your worksheet.
To create a calendar for a different month, just enter in cell A1 the date of the first day you want to display for the new month. If you want to make notations, enter them on the odd-numbered rows.
Find additional tips at http://www.pcworld.com/hereshow. Spreadsheets welcomes your tips and questions and pays $50 for published items. Contributing Editor Richard Scoville is a software training consultant in Chapel Hill, North Carolina.
