Background Information

In my first MPUG post about the Deadline dates feature in Microsoft Project, I documented how to set a Deadline date on a task to specify the “target” date for when that task needs to be completed. I personally like to use Deadline dates on milestone tasks, since milestones represent the start or finish of something important in the project.

One benefit of using Deadline dates is that Microsoft Project displays a green arrow symbol in the Gantt Chart pane on the date specified as the Deadline date. For example, notice in Figure 1 that I have specified Deadline dates on the milestones for the Development, Testing, and Pilot phases of the project, along with the final Project complete milestone task as well.

Figure 1: Deadline dates specified on four milestone tasks

In my way of thinking, the gap between a task’s Finish date and its Deadline date represents the “amount of time left for things to go wrong” on that task. If there is a gap of 5 days between these two dates, this means that the task can only slip 5 days before its Finish date will slip past its Deadline date. In other words, the task has only 5 days’ worth of “things going wrong” time before the task will miss its Deadline date.

I believe that most of us would like to know how much time we have left before we run out of time and the task finishes later than its Deadline date. Therefore, in this MPUG newsletter article, I want to show you how to create a custom field to calculate the number of days between the Finish date and the Deadline date for a task, and then to display a meaningful graphical indicator to help you interpret this information.

Creating the Custom Field

After setting the Deadline dates on a number of important milestone tasks in my project, I am ready to create a custom field to calculate the number of days between the Finish date and the Deadline date for each task. I would also like to display a graphical indicator to graphically display this information. To create this custom field, complete the following steps:

Click the Project tab to display the Project ribbon.
In the Properties section of the Project ribbon, click the Custom Fields button. Microsoft Project displays the Custom Fields dialog, such as the one shown in Figure 2.

3. In the upper-right corner of the Custom Fields dialog, click the Type pick list and select the Duration item on the pick list.

4. In the Custom Fields dialog, select the first unused custom Duration field and then click the Rename button. In the Rename Field dialog, enter a name for your custom field, such as shown in Figure 3. Notice that I entered the name Deadline Slack in the Rename Field dialog, but you can rename the field using any name that makes sense to you.

Figure 3: Rename a custom Duration field

5. In the Rename Field dialog, click the OK button.

6. Leave the custom field selected at the top of the dialog and then click the Formula button. Microsoft Project displays the Formula dialog, such as the one shown in Figure 4.

7. In the white workspace area in the middle of the Formula dialog, enter the following formula:

IIf([Deadline] = ProjDateValue(“NA”), “-16000d“, ProjDateDiff([Finish], [Deadline]))

The preceding formula works as follows:

The formula uses the ProjDateValue function to determine if the task has a Deadline date specified. If the task does not have a Deadline date, the Deadline field for that task contains an NA value.
If the task does not have a Deadline date entered, the formula returns a value of
-16000 days. This is a trick I like to use so that I can display a graphical indicator for tasks that have no Deadline date entered.
If the task does have a Deadline date specified, then I use the ProjDateDiff function to calculate the difference between the Finish date and the Deadline date for the task. Because the formula is in a custom Duration field, the difference between the two dates is displayed in Days by default.

I do recommend that you use the preceding formula exactly as written, since I know that the formula will work as intended.

8. When you finish entering the preceding formula, click the OK button to close the Formula dialog.

9. In the formula confirmation dialog shown in Figure 5, click the OK button.

After creating the formula for the custom Duration field, you need to configure the calculation setting that determines how Microsoft Project will calculate values in this field for summary tasks. You must do this regardless of whether you specify Deadline dates on summary tasks or not.

10. To configure this setting, select the Use formula option in the Calculation for task and group summary rows section of the Custom Field dialog, such as shown in Figure 6.

Figure 6: Calculation setting for summary tasks

The final steps in the creation of the custom field are to set up the graphical indicator criteria to display in this field.

11. Leave the custom field selected in the top of the dialog, and then click the Graphical Indicators button. Microsoft Project displays the Graphical Indicators dialog for the selected field, such as shown in Figure 7.

12. In the middle of the Graphical Indicators dialog, enter the following graphical indicator criteria as shown in Figure 8.

I use the preceding set of graphical indicator criteria to do the following:

If the custom field contains a value of -16,000 days for any task, Microsoft Project will display no graphical indicator for that task (the field will be blank).
If the custom field contains a value than is 2 days or less, the software will display the red unhappy face indicator for that task.
If the custom field contains a value that is 5 days or less (but is greater than 2 days), the software will display a yellow neutral face indicator for that task.
If the custom field contains a value that is greater than 5 days, the software will display the green happy face indicator for that task.

Obviously, you can specify your own graphical indicator criteria as you wish for the second, third, and fourth rows of the criteria table, but you must leave the first row as I specified to determine which indicator to display for tasks that have no Deadline date set.

13. At the top of the Graphical Indicators dialog, select the Summary rows option, and then select the Summary rows inherit criteria from nonsummary rows checkbox.

14. In the criteria confirmation dialog shown in Figure 9, click the Yes button.

15. At the top of the Graphical Indicator dialog, select the Project summary option, and then select the Project summary inherits criteria from summary rows checkbox.

16. In the criteria confirmation dialog, click the Yes button.

Figure 10 shows the completed graphical indicator criteria in the Graphical Indicators dialog.

Figure 10: Graphical Indicators dialog – indicator criteria entered

17. At the bottom of the Graphical Indicators dialog, leave the Show data values in ToolTips checkbox selected, and then click the OK button. With this option selected, you can float your mouse pointer over any cell in the custom field and see the underlying value in a Tooltip.

After creating the custom field with its graphical indicator criteria, you can display this custom field in any Task table, such as the Entry table, for example. For example, notice in Figure 11 that I inserted the custom Deadline Slack field into the Entry table in the Gantt Chart view. So that you can see that the custom field does display different graphical indicators, I adjusted the Duration value of several tasks so that the formula will generate the green, yellow, and red indicators. Also notice that the field displays no value at all for tasks that do not include a Deadline date.

Figure 11: Deadline Stack field displays graphical indicators

Saving the Custom Field to the Global.mpt File

After you create and test this custom field, you will probably want to use it in other projects. To make the custom field available to all of your projects, complete the following additional steps:

Click the File tab and then click the Info tab in the Backstage.
On the Info page in the Backstage, click the Organizer button.
In the Organizer dialog, click the Fields tab.
In the Fields page of the Organizer dialog, select the new custom field on the right side of the dialog, and then click the << Copy button, such as shown in Figure 12.

Figure 12: Organizer dialog – Fields page

The preceding action copies the new custom field from your project to the Global.mpt file on your hard drive. Every time you launch Microsoft Project, the software loads a copy of the Global.mpt file into memory so that you can access all of your default and custom objects, such as views, tables, filters, groups, and even fields.

5. Click the Close button to close the Organizer dialog.

6. Click the Save button in the Backstage to save the latest changes to your project.

I hope these first two MPUG newsletter articles will help you to be more proficient with using Deadline dates in your own projects, and a bit more proficient at writing formulas as well!

Heads Up: Keep your eyes open for two more articles about Deadline dates in Microsoft Project. My third article will discuss how Deadline dates can negatively impact Critical Path and Total Slack calculations. Because of the negative impacts of Deadline dates, the fourth article will show you how to use a custom Finish By field instead of using Deadline dates, and I will even how to display the Finish By date as a symbol in the Gantt Chart pane. That trick alone should be worth the price of admission!