Calculating National and Religious Holidays for Your Project Schedule

In November, 2014 I began an annual tradition: I collected a list of commonly observed national and religious holidays for the coming calendar year, and suggested that those holidays observed by the project team be accounted for as non-working days in project schedules for the coming year. But it’s time to remove myself from the equation: I’ve prepared an Excel workbook that will calculate my (now expanded) list of national and religious holidays, from 2021 through 2030.

How it Works

Many holidays are observed on a specific date, such as Canada Day. Organizations that observe these holidays usually have their own rules for what day to take off when they occur on a weekend, so excepting US Independence Day and Christmas, I don’t try to predict whether Friday or Monday will be non-working. Other holidays are based on relative dates such as third Monday of January, like Martin Luther King’s Birthday. In a couple of cases, the authorities added a wrinkle, such as last Monday in August. Others are based on a Lunar calendar; rather than try to calculate Lunar New Year or Passover, I created a look-up table and populated it through 2030.

Download the workbook using the link at the bottom of this page. Then enter the year you want to schedule for in the cell at the top of the Holidays tab, highlighted in orange.

Change Working Time in MS Project

Navigation depends on which version of project you are using. In Microsoft Project 2007, under the Tools menu, select Change Working Time.  In Project 2010 and later, on the Project tab, select Change Working Time.  You can then enter the holidays under the Exceptions tab.  Note that Exception days appear in the calendar in blue; however, if you have selected one of the exception dates, as shown in the example below, the date will appear in red.  Scheduled non-working days appear in gray.  Note that you can also make an exception of a scheduled non-working day, so that it appears to be a working day.  Use this feature carefully – having some of the team working over a weekend can easily throw off the scheduled for the entire team.

Creating a Custom Calendar

You can also create custom calendars, if your team is spread across multiple countries with different holidays. Again, the version of Microsoft Project you are using makes a difference in navigation. In Project 2007, under the Tools menu, select Change Working Time. In Project 2010 or later, on the Project tab, select Change Working Time. Click the Create New Calendar button in the upper right. Give the new calendar a meaningful name, then click the Make a copy of radio button. Select the Standard calendar from the pull-down list. Then click OK.

At this point, you can add the dates you want to mark as exceptions to the working calendar.  Enter the Name, Start, and Finish dates. Then click the Details button. Click the Working Times radio button.  The default working hours will appear; change them only if necessary.

Click OK to return to your custom calendar and enter the non-working dates that apply. Then assign each team member to the appropriate calendar using the Resource Sheet, in the column labeled Base.

Scheduling with Multiple Calendars

While it can be helpful to have MS Project automagically re-schedule after you make a change, be cognizant of what can happen when you have a summary task involving team members using different calendars. A change of one day in one detail task can cause the summary task completion date to change by two or more days. Scrutinize the results before you publish them, and investigate anything that seems wrong.

Once your career has progressed beyond managing a few folks co-located in one cube farm, your ability to think globally and manage a geographically distributed team will be key to how far you can go. Develop your multi-cultural knowledge, awareness, and communication skills, and when someone is needed to manage a project that crosses borders, you’ll be ready.

Holidays - Calculated
Holidays - Calculated
Holidays-Calculated.xlsx
15.3 KiB
19 Downloads
Details
Title
Article Name
Calculating National and Religious Holidays for Your Project Schedule
Description
I’ve prepared an Excel workbook that will calculate commonly observed national and religious holidays, from 2021 through 2030.
Author
The Practicing IT Project Manager LLC