To improve the presentation use the same method to align the day name in A6:G6 using Center for both Horizontal and Vertical.

This spreadsheet produces a versatile reusable calendar that can be printed out month by month

  

 

 A Calendar to Infinity

 

The DATE function used to be the one and only way to produce a date in a spreadsheet but given Excel's ability to recognise dates automatically its role has faded into obscurity. You have to be careful with syntax of the DATE function as it seems to be the wrong way round – you specify the year, then the month number and finally the day number. To see this in action open Excel and type in =DATE(1,2,3) – this produces the date 3-Feb-01. This hides another problem that only becomes apparent when you look at the date using a format that displays the year number in full. The cell contains the date 3 February 1901. So while you can drop the leading zeros in the month number and day number, don’t take any short cuts with the year. However, if you leave the year out when entering dates Excel will automatically add the current year.

 

Monday’s child

 

Another Excel function, WEEKDAY, provides a quick way of discovering the day of the week of any date. To try out this function type the date 25-Dec into cell A3 in Excel and then in B3 enter  =WEEKDAY(A3). You will see 6 appear there. That's not really what you want to see - it relies on you knowing that by default Excel counts from Sunday as 1 through to Saturday as 7. However, all you have to do if you don’t want to convert from 6 to Friday for yourself is to use another useful function – TEXT – which converts from a date serial value to a specified number format. The format to give a full weekday name is “dddd” (or for a day abbreviation is “dd”). Try this out by entering or by entering =TEXT(A3, “dddd”) in C3.

 

There are also three functions that extract the day, month and year numbers from a date serial number.  Again assuming that A3 contains the date 25-Dec, =DAY(A3) will return 25, =MONTH(A3) will return 12 and =YEAR(A3) will return 2009.

 

Our example spreadsheet makes uses of these functions to create a calendar display so that the user can specify any month of any year (in the range 1900 to 9999) and instantly be shown it on a standard calendar grid. It also makes use of  custom formats and in the final steps we add some cosmetic touches so that it can be printed out for use as a Month Planner.

 

Although the idea of a month-to-a-view grid seems commonplace in fact it is a remarkably tricky problem since there is such an irregular correspondence between weekday and month days and yet you always want the grid to run from the beginning of the week – i.e. from Sunday to Saturday irrespective of the day on which the month begins.  A month can have between 28 and 31 days and, depending on which day of the week the first of the month falls, can span a minimum of 4 and a maximum of 6 weeks. The clever part of our spreadsheet model is the formula that works out the parts of the grid that don’t fall into the month we are interested in. Rather than leaving these cells completely empty we’ll mark them with a discrete dot. This uses =IF, a function you are probably familiar with by now.

 

KEYPOINTS

Date Serial Number – Excel stores dates internally as numbers in the range 1 (corresponding to 1^st January 1900) to 2958465 (corresponding to 31^st December 9999). When you set the Number format of a cell containing a date to General you’ll see the date serial number.

 

Absolute references – Normally spreadsheet cell references are “relative” so that when you copy formulae referring to cells the new copies act on their neighbouring cells in the same way. When you want to refer to a fixed cell location you need to make it “absolute” by adding dollar signs. A quick way to do this is to press F4 as soon as you’ve typed in, or pointed to, a cell reference.

 

 

First we’ll create a calendar display with complete day names. Type Sunday into A6 and drag with the fill handle to G6 to enter the other day names automatically. For the purpose of the formula that places the days of the month in the correct position on the grid, we also need to enter the day numbers following the Excel convention that 1 equals Sunday, 2 equals Monday through to 7 for Saturday in Row 6. To save typing having entered 1 in A4 and 2 in B4 you can select these two cells and use the fill handle to extend this series to G4.

 

Next we need a formula that works out the day of the week on which the month starts. To simplify this enter the labels “Year” in A1 and “Month” in A2 and “First day” in A3. Then type 2010 in B1 and 1 in B2 and enter the formula  =WEEKDAY(DATE(B1,B2,1)) in B3. This returns the weekday number of the First day of the month specified in cell B2 in the year specified in B1.

 

We can now use this day number to devise a formula to enter the day numbers on the first row of the calendar grid. The formula to enter in A7 is =DATE($B$1,$B$2,1+A4-$B$3). The first problem with this is the display – it shows the full date and we simply want the day number. To solve this we’ll use a custom format to show the day number instead - but only after we entered all the formulas we need as they have the unwanted side effect of setting the formatting back to General.

 

The second problem with the formula in Step 3 is that it results in dates from the previous month being included in the first row. To overcome this we need to check whether the date falls within the month specified in B2. If it does then we enter the day number and if not we enter five spaces followed by a dot, to place a dot in the middle of the cell.  The formula that performs this uses an IF function and is entered in A7 as:

=IF(MONTH(DATE($B$1,$B$2,1+A4-$B$3))=$B$2,DATE($B$1,$B$2,1+A4-$B$3),"     .")

Copy this formula along the row to G7. This results in dots in the first five cells then date serial numbers under Friday and Saturday.

 

The day numbers for the second, third and fourth rows of the calendar grid can be produced by adding one to the previous day number. This can be done by entering two formula, using the fill handle in each case to copy the formula to the other cells where it applies. The formula to add one to the day number of the first Saturday in the month to give the correct number for the Sunday in A8 is G7+1. Copy this to down the column to A9 and A10 using the fill handle. To go from Sunday to Monday through to Saturday in each row you refer to the cell to the left and add 1. So type A8+1 into B8 and use the fill handle to copy this five cells to the left and two rows down.

 

The problem with using the same incremental method in the fifth and sixth weeks of the grid is that of rollover into the following month. Again we need an IF function to discover whether to enter the day number or a dot into the cell and once this happens adding 1 to the previous cell results simply in #VALUE. To get around this we add 7 to the values in the fourth row of the grid to produce the fifth week’s date and, as there will often be dots rather than dates in this row we’ll again rely on the values in the fourth row for the sixth row and in this case add 14 to them.

 

Enter the formula

=IF(MONTH(A10+7)=$B$2,A10+7,".") into A11. This IF function checks whether cell A11 falls within the specified month and if so enters the day number otherwise a dot. Similarly enter =IF(MONTH(A10+14)=$B$2,A10+14,".") into A12. Then use the fill handle to copy A11:A12 to B11:G12.

 

Now for the formatting, select the range A7:G12, click the right mouse button and choose Format Cells from the context menu. Click on Custom at the bottom of the Number tab of the dialog box and type d into the Type box and click the OK button at which point you will see day numbers replace all the date serial numbers.

 

At the moment we expect the user of the calendar to type in two items of information – a Year number and a month number. A couple of changes allows the user to enter month name and year as a single entry and have this displayed as the heading to the grid. Month names vary from three letters (May) to nine letters (September). To ensure the correct placement we’ll merge the cells A5 to G5 and centre the text string within them. Select A5 to G5  and click on the Merge and Center icon in the Formatting Toolbar. You may need to open this toolbar using the menu item View Toolbars and placing a tick against Formatting.

 

To make the month and year display as required needs another Custom format. Open the Format Cells dialog box again. You should find that Custom is already selected in the Category section in the Number Tab. Enter “mmmm yyyy” into the Type box. The click on the Font tab and choose the Font, here Arial, Font style, here Bold, and Font Size (here 12) that you require.

 

Now place the cursor within the merged cell in row 5 and type the date of Valentine's day using your preferred date format - 02/14/10 (for mm/dd/yy format in the US or 14/02/10 for dd/mm/yy which was used here). Although you will see the specific date in the Entry line you will see February 2004 appear over the middle of the grid.

 

We now need to enter formulae into B1 and B2, replacing the existing contents so that they use the entry in cell A5 as the basis for the calendar. Enter =YEAR(A5) into B1 and =MONTH(A5) into B2 and notice how the grid updates.

 

The final steps are all cosmetic and are in preparation for printing the grid to a page for use as a month planner. Before continuing Save the spreadsheet under the name Calendar.xls. You’ll find this file in the Codebin if you want to start at this point.

 

A month in print

From this point on work on a copy of the original spreadsheet as we are making extensive formatting changes and these may need to be adjusted to suit the printer and paper you actually use. Before continuing use the File, Page Setup command to choose the paper type and orientation you intend to use and using the Printer button ensure it is sending output to the Printer of your choice if more than one is available.

 

**pagesetup

 

A plain landscape display

 

For printing on letter or A4 in landscape orientation we first need to widen the columns. To do this select columns A to G using their heading labels, right-click the mouse, click on Column Width and type 20 into the box that opens.

 

Now select rows 7 to 12, right-click the mouse, click on Row Height and type 70 into the box that opens. Also select rows 5 to 6 and set their row height to 25.

 

Now add borders. First select the whole calendar grid plus headings, i.e. A5:G12, right-click, select Format Cells and click on the Border tab. Click on both Outline and Inside, choose the thick solid line in the Style box and click on OK.

 

To give space to write appointments in each box we’ll place the numbers at the top left of the cells. Select A7:G12 and re-open the Format Cells dialog box and go to the Alignment section. Choose Left (Indent) from the drop down list of Horizontal options and Top from the drop down list of Vertical options then click OK.

To improve the presentation use the same method to align the day name in A6:G6 using Center for both Horizontal and Vertical.

 

Now choose the range A5:G12 and from the File menu click Print Area and choose Set Print area. The area to be printed is then delineated and you can proceed to click the Print Preview button.

 

Before printing go to the Margins tab in the Page Setup dialog and adjust the margins as you require and place ticks in both the Horizontal and Vertical boxes in the Center on Page section.

 

If you discover that the document is going to print over more than one page use the Fit to button in the Page Tab of the page Setup dialog:

 

PSD

 

Add an illustration

 

If you want a portrait layout there is also space for a drawing or a photo. Again working on a copy of the original spreadsheet, first change the column widths and row heights as above, this time using 12 as the width and 40 as the height for the calendar grid. Next use the Format cells dialog and make selections to in the Alignment tab to control the placement of the numbers in the grid and add the grid lines using the Border section.  

 

To make the space for the illustration inserting 2 rows after row 5. Merge both of them across columns A to G and set the row height of row 6 to 400. Then select A6, right-click, choose Format Cells and use the Border tab and the Outline preset to draw a frame. The empty frame could be used for drawing on or pasting in a photo.

 

If you prefer to insert some existing Clip Art or photo stored on the computer, place the cursor in A6 and use the command Insert,Picture and choose Clip Art or Photo as appropriate and then navigate to the item you want.

 

The picture initially inserted is likely to be far too big. To deal with it use the View menu to open the Picture Toolbar and use the Size option in the Format Picture dialog to get an approximate size. You can then adjust its size and position interactively with its handles.