Creating Holiday Calendars: Part Three

The third method makes heavy use of Excel and Word and the calendar is not created automatically. I was able to create the basic calendar pages, start to finish, in less than an hour. Adding pictures, however, took me forever! I scanned some new pictures and went through all my files picking and choosing large pictures for each month and for the individual blank days. Regardless of the method used to create a calendar, choosing and editing pictures will always take a lot of time. Here are the example pages. I have not included the large picture pages with these examples.

• January 2013
• February 2013

The first phase in this creating this calendar is creating a list of all the events you want to include. Although this example includes only births and marriages, one could include any TMG event; for example, imagine a calendar for your military historian that includes dates of enlistment for all the military servicemen and women in your TMG database. The names could even be color-coded to indicate the war in which each one served.

• Select the people to include by running a "List of People" report and changing the Temporary flag in the Secondary Output tab.
• Run a "List of Events" report that includes all the events you want in your calendar. The filter is shown here:

• This list is saved to an Excel file. The file will be sorted first by month and then by day to allow easy data copy-and-paste to a calendar. Columns will include the individual's name, tag label, and year of the event. Optionally, include flag values to allow easy color-coding; for example, enter maternal ancestors in one color and paternal ancestors in another. Here are the Output Columns used in my calendar.

• Now comes the fun part. Warning: If you are not familiar with Excel or other spreadsheet programs, you may want to skip this method entirely and opt for calendar creating methods one or two.
• Once you have your Excel file, delete any events that don't include months. If you choose, you could add these to a page at the end of the calendar, but if you don't know the month, you don't want to include these events in the calendar pages.
• Add an additional sort value to your file. Sort first by month in ascending order, then by day in ascending order, and then by year in descending order. This lists events first by month, then by day within month, and finally by event within day, most recent one first.
• Concatenate your column values to create event statements. You will need to create separate statements for each Tag Label, but this is easily done. First, filter the Tag Label column for one event type. Next, create your concatenation formula in the next available column. Finally, copy the formula down to the last row. Repeat for each event type included in the report.
• The concatenation formulas used in the example calendar are shown here. Remember that the ampersand (&) connects cell values to other cell values and to text. All text, letters, spaces, symbols, are surrounded by quotes.




• Although it looks like these statements could be copied and pasted to a calendar, this column actually contains formulas, not values. Highlight this formula column and copy it (Ctrl-C). Next, highlight the adjacent blank column, and follow this menu: Edit > Paste Special > Values > Okay. Voila! This column contains statements that can be pasted to your calendar.
• Microsoft Word 2000 and 2003 come with a Calendar Wizard. If you have Word 2007 or 2010, you can download the Calendar Wizard from the Microsoft website. There are three templates to choose from, and all would work for an ancestor event calendar. My example calendar uses the "Boxes & borders" template, but I reformatted several aspects. Word templates are very easy to reformat. You can change font styles, sizes and colors; change line colors, change backgrounds.
• Once you have your calendar template, return to your Excel file. You're going to format the text in that final column. Set the font you like in a size between 8 and 10 points, preferably the larger. Choose a color you like. Set the column width to fit the calendar box width. My calendar used a column width of 17.71, Monotype Corsiva font, 10 point, red. Now format the cells alignment to allow text wrapping.
• Highlight each day's events, copy (Ctrl-C), and then paste into the appropriate day block on your calendar (Ctrl-V). Repeat to the end of the year. Note that the blocks resize to include all the events.
• Events with a known month but an unknown day can be added to a blank block in the month.
• Add a small picture to one blank block in each month.
• Add symbols for holidays, if you choose.

This method is definitely not the easiest, but it does allow the most options. If you want to spend some money, the program WinCalendar supposedly allows importing of events from Excel and CSV files. The version that does that, though, costs $99, and I'm too cheap to spend that much just to test a program. This would be the ideal combination, though: TMG's flexible "List of Events" report to an Excel file that can be imported into a powerful calendar creator program.

I've now created calendars for my nephew and for my daughter. Next on my list is a tombstone-themed calendar for my son, who shares my fondness for cemeteries.

Happy Holidays, everyone!