Working with Multiple Task Calendars in Microsoft Project
Ever tried working with multiple task calendars in Microsoft Project and could not understand why the durations in days or weeks were not as expected? In this post, we will look at the problem and a workaround.
In the schedule we will be using in this example, the calendar options for Hours per Time Period are shown below (i.e. Hours per day, Hours per week and Hours per month).
These definitions influence how Start and FinishDates are calculated irrespective of the calendar assigned tasks (unlike Primavera P6, MS Project does not have facility to define Hours per Time Period for each calendar).
The 3 calendars we will be using in this example are:
Standard: This is MS Project's default calendar with 8 hours a day working period
10 hrs / 5 Day Week: This is a calendar I created for 10 hours a day working period
12 hrs / 5 Day Week: This is a calendar I created for 12 hours a day working period
Our schedule has 3 group of activities and each group has 3 activities of 1 day, 5 days and 10 days respectively. Group 1 activities are assigned Standard calendar, Group 2 activities are assigned 10 hrs / 5 Day Week calendar and Group 3 activities are assigned 12 hrs / 5 Day Week calendar.
Task 3 (ID4) under Group 1 has a 10 day duration, starts on Monday 4th January and ends on Friday15th January, but Task 3 (ID8) under Group 2 has a 10 day duration, starts on Monday 4th January and ends on Wednesday13th January and Task 3 (ID12) under Group 3 has a 10 day duration, starts on Monday 4th January and ends on Tuesday 12th January.
You might want to ask yourself the question, how come these 3 activities have different end dates? This is because 1 day is defined as 8 hours under calendar options for Hours per Time Period (see figure in second paragraph). MS Project converts the 10 day duration to 80 hours (8 x 10) and then distributes this 80 hours over the working periods defined in each calendar hence we have 3 different Finish Dates.
Workaround
Since each of our 3 calendars reflect working hours in a work week with no weekend work, the desire is for our duration column to show 10 days with all 3 activities finishing on same date.If only we are able to define custom Hours per Time Period for each calendar, then we will easily achieve the desired output as in Primavera P6. But since we cannot define individual Hours per Time Period, we will have to come up with a workaround using Custom Fields.
First thing to do is instead of entering duration in days, let enter activity durations in hours (since the working periods in our calendars are defined in hours).
For Group 1 activities, we'll enter 8 hours for Task 1 to represent 1 day since Group 1 activities calendar is 8-hrs / 5 Day Week. For Task 2, it will be 40 hours (8 hours x 5 days) and Task 3 will be 80 hours (8 hours x 10 days).
For Group 2 activities, we'll enter 10 hours for Task 1 to represent 1 day since Group 2 activities calendar is 10-hrs / 5 Day Week. For Task 2, it will be 50 hours (10 hours x 5 days) and Task 3 will be 100 hours (10 hours x 10 days).
For Group 3 activities, we'll enter 12 hours for Task 1 to represent 1 day since Group 3 activities calendar is 12-hrs / 5 Day Week. For Task 2, it will be 60 hours (12 hours x 5 days) and Task 3 will be 120 hours (12 hours x 10 days).
First thing you will notice is that the activities all Finish Dates as expected (all Task 1 finish on Monday 4th January, all Task 2 finish on Friday 8th January and all Task 3 finish on Friday15th January) but unfortunately the Summary duration for Group 2 & Group 3 activities have all gone pear shape. This is because Summary duration is still in days (auto calculated by MS Project) using the default Hours per Time Period definition of 8 hours per day.
Now that we have solved the problem of display the correct Finish Date, next we need a Duration column that where the units will be in Days with the correct Summary duration and Finish Dates. To do this, reposition the Duration column to after the Task Calendar column and then insert a new column, Number1 (this is a Custom Field).
With your cursor on any cell in the Number1 column, from the ribbon click on Project > Custom Fields or Format > Custom Fields.
This will open the Custom Fields dialog box for Number1 and then click the Rename button to change the name of the field from Number1 to Task Dur (Days)
With the field Task Dur (Days) still selected, click on Formula button in the Custom attributes section to open the Formula dialog box, enter the formula below and click OK.
IIf([Task Calendar]="12 hrs / 5 Day Week",(ProjDateDiff([Start],[Finish],"12 hrs / 5 Day Week"))/(60*12),IIf([Task Calendar]="10 hrs / 5 Day Week",(ProjDateDiff([Start],[Finish],"10 hrs / 5 Day Week"))/(60*10),(ProjDateDiff([Start],[Finish],"Standard"))/(60*8)))
With the field Task Dur (Days) still selected, tick the Use Formula checkbox in the Calculation for task and group summary rows section and click OK to return to our Gantt chart table.
In our Gantt chart, you will notice that the new custom field Task Dur (Days) now displays the right durations in days with the right Finish Dates (even in the Summary activity).
Notice we this have the default Duration column display, we need to remove it from view by dragging the vertical divider to the left till it covers the Duration column or right-click the Duration column title and click on Hide Columns.
And that is our workaround solution to the problem of multiple calendars.
Formula Explanation
Our formula uses 2 nested IIF statements. The first IIF statement is IIF (A, B, C)
where:
A = [Task Calendar]="12 hrs / 5 Day Week"
B = (ProjDateDiff([Start],[Finish],"12 hrs / 5 Day Week"))/(60*12)
C = IIF (D, E, F)
This first IIF statement checks to see if the activity uses the 12 hrs / 5 Day Week calendar and if yes, it calculates the duration of the activity by its subtracting Start Date from its Finish Date using the working periods defined in the 12 hrs / 5 Day Week calendar, since the function ProjDateDiff returns duration in minutes, we then divide the result by 60*12 (minutes per hour multiplied by working hours in selected calendar).
But if the activity does not use the calendar 12 hrs / 5 Day Week, then the second IIF statement is executed where:
D = [Task Calendar]="10 hrs / 5 Day Week"
E = (ProjDateDiff([Start],[Finish],"10 hrs / 5 Day Week"))/(60*10)
F = (ProjDateDiff([Start],[Finish],"Standard"))/(60*8))
This second IIF statement checks to see if the activity uses the 10 hrs / 5 Day Week calendar and if yes, it calculates the duration of the activity by its subtracting Start Date from its Finish Date using the working periods defined in the 10 hrs / 5 Day Week calendar, since the function ProjDateDiff returns duration in minutes, we then divide the result by 60*10 (minutes per hour multiplied by working hours in selected calendar). But if this second IIF statement does not use the 10 hrs / 5 Day Week calendar, then we calculate activity duration using the Standard calendar.
