Please note that I have modified the content of this article by adding the detailed description for the calendar and that I have shortened the article “Our contribution to the Microsoft 2011 Calendar Contest – US-Version” which previously included the description. The reason for this is that I would like to avoid confusions between the versions of the country specific calendars and want to keep in sync with the German and English articles.
On the occasion, when developing the German version of the calendar, I have added some new features to the calendar and updated the US-Version. And I have also fixed some minor bugs and extended the holiday calculation algorithms. These algorithms automatically calculate the dates of the holidays for the selected year, both fixed to a date and relative to each other. So, in general, there is no need to reenter the holidays each year.
Update on 01/05/2012: Please note, that the calendar can also be used for 2012. Just modify the year on the settings sheet.
At this point I would firstly like to thank Andreas for also testing the new version. And also thank Claudia for her support to the design. Of course, the current version for the calendar can be downloaded at the end of this article.
The calendar, originally developed for the Microsoft Office Calendar 2011 Contest, was made in Excel 2010 and has a similar layout to the Excel Soccer World Cup 2010 Planner, which has been downloaded over 375,000 times from its website. A navigation bar on the top of each sheet in the calendar allows you to easily navigate through the calendar.
The calendar includes a week, a month, a quarter and a year view. The figure above shows a screenshot of the quarter view. Holidays and notes are respectively encircled in blue and red. This is a new feature and was not present in the previous version. However if a note exists for a day which is also a holiday, the blue circle is drawn, meaning that holidays are preferred. Please note that, the week numbers are only displayed if a valid calculation method has been chosen in the settings sheet.
The information is displayed much more detailed in the week view. Up to three notes for one day are displayed including their long descriptions (if present). On the right side of each note, you can find a hyperlink to the source line in the notes sheet. Holidays are displayed below the notes. The upper right corner now contains a scrollable combo box for better selecting the weeks. The screenshot below shows a sample of this view.
The month view only displays the short descriptions of each note and their hyperlinks. Holidays are displayed below the notes and colored in blue. If a note exceeds a specified number of characters, the note is shortened.
The year view (see figure below) displays all holidays and the first note entered for a day. The week numbers are only displayed if a valid calculation method is active. I have fixed some bugs in this view concerning some conditional formats and the shortening of notes.
I have extended the worksheet for entering the holiday by some new features, especially the section for calculating relative holidays. This sheet includes three sections for entering the holidays. The first section “Fixed” allows entering fixed holidays, for example the New Years’ Day or the Independence Day.
The second section “Calculated” allows entering holidays which are calculated using a simple logic. The only exception is the Easter Sunday, which uses the Gauss Formula. If you enter a new holiday in this section, you may select the month for this day and indicate the weekday and the position of the holiday. For example, the Memorial Day in the United States is defined as the last Monday in May. The selections may then be “May”, “Last” and “Monday”. Another example is the Thanksgiving Day, which takes place on the fourth Thursday of October. So the selections may be “October”, “Fourth” and “Thursday”.
The last section “Relative” is used for more complex calculations of holidays. Although these calculations may not affect official holidays in the United States, I have added this section to the calendar as this section allows the calculation of holidays relatively to previously defined holidays. For example the first Advent is defined as the fourth Sunday before the Christmas Day. So we may firstly get the date of the Sunday before Christmas and then subtract 21 Days from this date. The settings may be “Christmas Day”, “Previous Sunday” and then “Minus” and “21 days”. The calendar includes some samples, which are set to “inactive” and therefore not displayed on the calendar sheets.
This version of the calendar (EN-US) already includes the official holidays for the United States (as I found the on Wikipedia and other official sites). Of course, you can add some country specific holidays. A warning appears in the Results’ column if a holiday has been entered twice or more times.
You can add up to 128 notes to the notes sheet and associate each note with a day. Please note, that, if you enter more than one note for a day, not all views will display all notes for that day. The maximum is 3 notes per day. A warning icon appears on the right side of the sheet, if you enter more than 3 notes for a day.
The settings sheet is divided into the three sections “Global options”, “Display options” and “Calculation options”. In the first section, you can setup the year and the first day of the week used in this calendar. You can also choose the weekend days and the day of rest used in this calendar, which are selected by default on Saturday and Sunday. Making changes to these options may be less relevant for western countries, however in some Arabic countries, the weekend days are Thursday and Friday.
The section for the display options includes some settings concerning the views in the calendar. For example, you can select if the hyperlinks for the notes may be displayed or not. An important option is the option for selecting the date format. If you are using an English Operating System and an English Excel version, the default setting “MM/DD/YYYY” may work for you. However, if you are for example using a German Operating System, you may change the date format to the German format to “MM/TT/JJJJ”. I have included the default German, French and English date formats. You can also enter up to two new user specific date formats in the resource sheet. The week view displays the message “Please check the date format in the settings sheet”, if an invalid date format is used.
The last section allows selecting the calculation method for the week numbers. The first method, used in Excel by default, assumes that the first week is the week including January, 1th. The second method is, for example, used in Europe and based on the ISO 8601 standard. The first week is, in this case, the week including at least 4 days in January, meaning the week including the first Thursday in January. However, is you use this method; the first weekday may be Monday.
The calendar also includes a welcome sheet with some information how to use the calendar. All sheets are password protected; however you can easily remove it as the used password is an empty string. Please do not delete or insert lines to the sheets in the calendar and please, do not move the cells in order to preserve the integrity of the formulas.
The only sheet which cannot be reached by an own icon from the navigation bar is the resource sheet including all texts used in the calendar, except the copyright and welcome sheet. However, you can reach the resource sheet from the settings sheet by clicking on “Next” or by using the Excel sheet tabs.
Please do not reorder the entries and consider that if you change the values used for the lists on other sheets, you may have to repeat you selections on that sheets; for example the months. A reason why we have used data validations lists instead of form controls is that workbooks using these controls cannot be uploaded to the Office Template Website. The resource sheet also includes two fields for entering user specific date formats.
Finally, the template includes a copyright sheet with some information and the license terms. We are providing to you, the user a limited, non-exclusive, free license for this product. However, we own the title, copyright and all other commercial and non-commercial rights to this calendar. It is not allowed to remove or modify the existing copyright and trademark notices in this calendar. The use of this calendar grants you no rights to our trademarks or service marks.
An important point, I would like to point explicitly (due to some bad, but fortunately only in a few cases, experiences with our Excel Soccer Planner) is that this calendar may be not, without any exception, offered or linked from any fee-based website or portals. This especially applies to websites which will leave the users pay money for accessing download services.
Finally, please feel free to send us suggestions, improvements or your feedback to the calendar. You can leave a comment in this blog or send us an email to the address mentioned in the Imprint of this website. I have also created a new category in this blog for the calendar.
We will continue developing the calendar; for example new localized versions will be released and new options added. Have fun.