Mastering Custom Field Formulas in MS Project | Articles


By Ismet Kocaman

 

 

 

Bottom-Up Planning with Manual Scheduling

 

Version 1, Published on October 21st, 2015

Please first see Disclaimer, Copyright Notes and Trademark Notes sections on the bottom of this page. This document is related to all

standalone desktop versions of MS Project Professional/Standard supporting the manual-scheduling feature.

 

Suppose that we are at the initial stage of creating a project plan, and technical project team has prepared a basic plan which is simply a task list with names and start/finish dates, as shown below:

Job Name

Start

Finish

T1

Sep 9 '15

Sep 10 '15

T2

Sep 10 '15

Sep 14 '15

T3

Sep 11 '15

Sep 14 '15

 

Note that in cross-functional projects implemented in manufacturing environments, there may be team members who are not familiar with the project planning process and they may prefer to estimate the start and finish dates instead of the durations for the tasks that they are going to perform in their part of the project.

 

Let us enter all the information provided by the team to Project. We now have a schedule composed of manually scheduled tasks with start dates and finish dates:

 

 

Project calculates durations for the manually scheduled tasks by using the dates entered. We will now switch to the Gantt Chart view and modify the task table by adding some other fields and a new task bar definition; see it below:

 

 

The Gantt Chart above shows a second bar with a task name inside, below each manual task bar, since a new bar definition has been inserted into the Bar Styles dialog box, on the line just above the Manual Task line. This new bar represents the “scheduled” dates for a manually scheduled task and its details are as follows:

 

            Name                          : Scheduled Task

            Show For ... Tasks      : Normal; Active; Manually Scheduled

            Row                             : 2

            From                           : Scheduled Start

            To                                : Scheduled Finish

 

The bar definition also includes a setting in the Text tab as follows: Text | Inside | Text1. The Text1 field’s formula references the Name field. This new bar, just like a shadow of a manual task bar, will help us to see how the “scheduled” dates change as we work on the schedule of the manually scheduled tasks.

 

In addition to visual representation of “scheduled” task bars on the Gantt Chart, the related fields have also been inserted to the current table in order to see how the “scheduled” data changes as we work on the plan.

 

The schedule does not contain any placeholder tasks. At this point, we do not have information on task dependencies. Also note that we do not have summary tasks in the schedule since the phases of the project have not been discussed yet. We will continue to work on the plan with the team by using the bottom-up approach, but while doing this, we just want to see whether a manually scheduled task starts earlier or later than its “scheduled” start date, as well as the difference between the two dates, based on the calendar days. We can use the following custom text field formula for this purpose:

 

iif( Not [Placeholder] And [Task Mode]; Switch(

[Start]>[Scheduled Start]; ([Start]-[Scheduled Start]) & " |<";

[Start]<[Scheduled Start]; ">| " & ( [Scheduled Start] - [Start]); True;"") ; "")

or

iif( Not [Placeholder] And [Task Mode], Switch(

[Start]>[Scheduled Start], ([Start]-[Scheduled Start]) & Chrw("&H25C4"),

[Start]<[Scheduled Start], Chrw("&H25BA") & ([Scheduled Start]-[Start]),

True,""),"")

 

In the formula, Chrw("&H25C4") returns left arrow symbol (<) while Chrw("&H25BA") returns right arrow symbol (>). The formula returns a zero-length string for a placeholder task or an automatically scheduled task, since the logical expression Not [Placeholder] And [Task Mode], which represents the condition tested returns false in either case. The formula also returns a zero-length string when the manually scheduled task starts at its scheduled start date (see the True pair in the Switch function).

 

The picture below shows what the formula initially returns in the example schedule; no difference for T1, T2 starts 1 day later and T3 starts 2 day later than the “scheduled” dates:

 

 

 

As it is seen in the picture above, the formula will initially show the difference between the project start date and the start of a manually scheduled task since there are no task dependencies defined yet, and therefore, the “scheduled” bars are all start at the project start date. 

 

We will now define task dependencies, but firstly, let us discuss some basics on linking manually scheduled tasks. When we create a task dependency between any two manually scheduled tasks for the first time, Project will move the successor based on the link, and also the “scheduled” dates of the successor will be updated accordingly. But note that Project will not update the link afterwards as required by any change in the predecessor’s schedule; for example, a change in duration. Also consider a group of manually scheduled tasks where some of the tasks already have links among them in the group; if we select the whole group and link them (for example, by the default finish-to-start dependency), then Project will reschedule all the tasks in the group. On the other hand, Project will not reschedule the tasks previously linked in the group, unless we also select them at the beginning.

 

Suppose that we have now information on the task dependencies as follows: all three tasks are linked with Finish-to-Start dependency. We will link the tasks, but we do not want Project to move manual tasks while entering dependencies (that is, our purpose is to create dependencies only for information), since we need to fine tune them later on a one-by-one basis by discussing the dates and durations with the team. There is a related Project option setting located in the “Scheduling options for this project” section of the Schedule tab of the Project Options dialog box; it is the checkbox;

 

Update Manually Scheduled tasks when editing links

 

which is turned on by default. This is a local setting effective in the active project plan file. Also note that this setting is effective only while editing links, such as linking tasks for the first time. Otherwise, for example, changing duration of a manually scheduled predecessor does not move the successor linked even when this check box is turned on; and this situation creates a task dependency conflict, which can be easily solved by selecting the successor and clicking the Respect Links command or even by selecting both tasks and clicking the task link command again if the check box is selected. Let us now turn that checkbox off and create task links. After having set the task dependencies, the schedule will look like this:

 

 

Note that the Text2 field now shows different values since Project updates the “scheduled” dates based on the task dependencies while keeping the manually scheduled dates unchanged:

 

  • T2’s “scheduled” start date is now Sep 11 08:00 according to the finish date (Sep 10 17:00) of the manual predecessor T1.

  • T3’s “scheduled” start date is now Sep 15 08:00 according to the finish date (Sep 14 17:00) of the manual predecessor T2.

 

Now the results from the formula can be interpreted as follows, for the manually scheduled tasks:

 

  • A right arrow and a number (>number) show the number of calendar days that the manually scheduled task may need to be delayed in order to start at its scheduled start date.

  • A left arrow and a number (number<) show how many calendar days earlier the manually scheduled task may need to be started in order to start at its scheduled start date.

 

In other words, either kind of the results from the formula tells us how a manually scheduled task would move when we change its task mode to “auto scheduled” or when we somehow force that task to move according to the links with the predecessors, without changing its task mode.

 

Suppose that we have now completed reviewing the schedule and already discussed all the information shown in the Text2 column with the team, and then decided to move T3 by 4 calendar days, as required by the dependency links. So we can proceed as follows:

 

  • Clicking T3 to select it on the table and then clicking the Respect Links command (TASK | (Schedule) Respect Links). The schedule now looks like as shown below (note that Project highlights the data changed in the fields):

 

 

We still have a dependency problem on T2. Decreasing T1’s duration to 1 day would fix the problem, but say, the team suggests otherwise, so let us just apply Respect Links to T2 as well. That creates another dependency problem on T3 as it is seen below:

 

 

This time, instead of applying Respect Links on T3 again, we will decrease its driving predecessor’s (which is obviously T2) estimated duration to 2 days by entering 2 to the Duration field, as suggested by the team.

 

Note    In a large schedule, it may not be easy to locate the driving predecessors of a task. In this case, Project’s Task Path tool can be used to highlight the driving predecessors of a task.

 

Also note that, if the dependency between T2 and T3 is not really required (i.e., a preferred dependency), then we may consider not to use it. But in this case, we must apply a “Start No Earlier Than” constraint to T3 while changing the task mode to the “auto scheduled” later.

 

As a result, the following is the final schedule with no dependency problems:

 

 

We can now change the task mode to “auto scheduled” for all the tasks so that the scheduling engine takes the control and then we can continue to work on the other aspects of the plan, such as determining the phases, defining the corresponding summary tasks and discussing the total duration of each phase, and so on. 

 

While working on the initial plan, it may be difficult to review and resolve all the dependency problems, especially complex ones, on a one-by-one basis in a large schedule of manually scheduled tasks. Therefore, it may be practical to focus on the tasks displaying large values in the Text2 field (that is, large dependency requirements) and to fix the dependency problems only for these tasks. And then we can select all the other tasks at once and click Respect Links. It is also possible to use some graphical indicators in the Text2 field to quickly see the values exceeding a certain threshold. Also consider that it may not be practical to do initial scheduling in “manually scheduled” mode, when the task list is a long one with a lot of short-duration tasks, and task relations are complex and not easy to see at the beginning.

 

 

Switching from Manual Scheduling to Auto Scheduling

 

Now consider the following schedule, composed of manually scheduled tasks, prepared by another technical team. Based on the values displayed in the Text2 field, T1 starts 2 days earlier than the project start date, therefore clicking Respect Links will have no effect on T1. Both T2 and T3 start 1 day later than the dates required by the dependency links, and clicking Respect Links can move both tasks back to their “scheduled” start dates.

 

 

Suppose that we have now decided to switch to the “auto scheduled” mode, but at the same time, we want the task start dates remain unchanged. So how can we do that while maintaining the task dependencies already set ?

 

In Project, there is a setting that is used for this purpose and it is located in the “Scheduling options for this project” section of the Schedule tab of the Project Options dialog box; it is the checkbox;

 

Keep task on nearest working day when changing to Automatically Scheduled mode

 

This checkbox is turned off by default and it is a local setting which is effective in the active project plan file when selected. Let us turn this option on and then change task mode to “auto scheduled” for all three tasks. As it is seen below, Project applies constraints to the tasks to keep them on the nearest working day:

 

 

But we do not want any constraints on the schedule because these tasks do not have such requirements. Suppose that we have discussed the schedule with the team and decided to proceed as follows:

 

  • Undo the changes (including the checkbox setting).

  • In the original schedule; change the project start date to Sep 7, 2015 (2 days earlier than the current one).

  • Apply 1-day lags to the Finish-to-Start dependencies on both T2 and T3.

It may be difficult to see the lags when the timescale period is not large enough to show them or when the Predecessors (or the Successors) column is hidden; therefore, always document them by entering information to the task notes.

 

 So the following is the final schedule:

 

 

We can now switch to automatic scheduling for all three tasks. The schedule of the auto-scheduled tasks without constraints looks like this:

 

 

We can now continue to work on the project plan by using the edited schedule above.

 

 

Using the Task Inspector

 

The purpose of this example is to demonstrate how to spot some simple scheduling problems by using a custom field formula referencing the fields Placeholder, Task Mode, Start and Scheduled Start. Unlike the simple ones discussed here, the scheduling problems in large and complex schedules would be difficult to locate and solve. In such a case, it is best to use Project’s advanced tool, Task Inspector (especially, while working with manually scheduled tasks) which analyzes the schedule constantly and helps us to solve the current and potential scheduling problems (including the ones that has been discussed in this example). For example, you can now open the Task Inspector pane by using the command TASK | (Tasks) Inspect, and review the information displayed in the pane as we progress through the case discussed below.

 

Text2 field’s formula can be modified to show the difference in workdays, instead of calendar days; consider the schedule below that shows a task with a task calendar where all weekdays are workdays. The “scheduled” start date is 5 calendar days earlier than the start date as it is seen in the Text2 field (Task Inspector will show the suggestion message "Task can start 5 days earlier"). The Text3 field’s formula,

 

ProjDateDiff( [Start], [Scheduled Start] ) / [Minutes Per Day]

 

shows a difference of -5 work days. The ProjDateDiff function uses the project calendar for the third parameter omitted by default. If there is a task calendar, the function uses it instead of the project calendar by default, so there is no need to specify the calendar as the third parameter.

 

 

Let us remove the task calendar and see how the schedule changes. As shown below, the Text3 field formula now returns -3 workdays since the 12th and 13th days are defined as nonworking days in the project calendar (Task Inspector will show the suggestion message "Task can start 3 days earlier").

 

 

 


Revision History:

Version 1 - October 21st, 2015 - Initial document


 

 

 

Disclaimer

The information contained in this document is intended only for the general interest of its readers and should not be used as a basis for making any business or other important decisions. Though all the efforts have been made to create accurate content, mistakes can occur. The author of this document cannot, therefore, guarantee the accuracy of content. The author of this document disclaims all warranties and must advise you to use this document at your own risk. The author of this document is not liable for loss of any nature resulting from the use of or reliance upon the information found therein.

 

Copyright Notes

All Content Copyright © Ismet Kocaman | ikocaman.pm[at]gmail.com | No part of this document may be reproduced, stored in a retrieval system or transmitted in any form or by any means, without the prior written permission of the Author (Ismet Kocaman). Do not quote/refer to the parts of the document and do not use it as training material without permission. 

 

Trademark Notes

Microsoft® is a registered trademark or trademark of Microsoft Corporation in the United States and/or other countries. The author of this document has no affiliation with Microsoft Corporation. All other trademarks mentioned herein are the property of their respective owners. Screen captures were used with authorization from Microsoft Corporation. This document is not a product of Microsoft Corporation.