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

**Finish**

**Dates**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 4

^{th}January and ends on

**Friday**

**15**, but

^{th}January**Task 3**(

**ID8**) under

**Group 2**has a 10 day duration, starts on Monday 4

^{th}January and ends on

**Wednesday**

**13**and

^{th}January**Task 3**(

**ID12**) under

**Group 3**has a 10 day duration, starts on Monday 4

^{th}January and ends on

**Tuesday 12**.

^{th}JanuaryYou 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 4**, all Task 2 finish on

^{th}January**Friday 8**and all Task 3 finish on

^{th}January**Friday**

**15**) but unfortunately the

^{th}January**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.

This is one of the reasons why professional schedulers don't use MS Project. Simple function such a creating multiple calendars becomes a nightmare in MS Project. It is a no brainier that some projects have multiple work hours.

ReplyDeleteI need to show Blockout Dates for specific tasks. For example, We can only do work in classrooms over the summer but we can do hallways year round.

ReplyDeleteFollow the steps in this post https://plannerstips.blogspot.co.uk/2016/01/highlight-time-periods-in-p6-and-ms.html

Delete