In this tutorial, we will create two custom fields that can be used individually or together to quickly and easily determine the status of each task. We are going to build off of the knowledge that we learned in our Statusing the Project Schedule tutorial. If you haven’t read that tutorial yet, you might want to check it out and then come back to this tutorial once you understand the statusing process…

Let’s start by first looking at the different scenarios that we may find in our project schedules that are related to task statusing.

The Statusing Scenarios

There are actually six different scenarios that we may find in our schedule that are related to task statusing. As we review each of these scenarios, remember that the Status Date establishes the date that we are statusing the schedule through. The Status Date is our reference point. Anything before the Status Date is in the past. And anything after the Status Date is the future. So our Status Date must be set if we are going to evaluate whether or not our tasks are statused properly. Now let’s take a look at each scenario…

Scenario 1: Invalid Task Dates in the Past

In this first scenario, we have a task that has either a forecasted Start or forecasted Finish date in the past without an Actual Start or Actual Finish date. This is considered an invalid task. The past has already occurred. There cannot be a forecasted Start or forecasted Finish date in the past without an Actual Start or Actual Finish. To resolve this situation, assign a valid Actual Start or Actual Finish date to the task or reschedule the task’s forecasted Start or forecasted Finish to a valid future date that is later than the Status Date.

Let’s look at an example…

In the below schedule, we see that the Status Date is set for 1/5/2018. We also see a single task that has a forecasted Start of 1/1/2018 and a forecasted Finish of 1/3/2018 but no Actual Start or Actual Finish dates. Both of these dates are in the past. Remember… The past is any date before the Status Date. Because these dates are in the past, we should also see Actual Start and Actual Finish dates. This is an invalid task that needs to be updated.

If the task did start, then an Actual Start date needs to be assigned. If the task did finish, then an Actual Finish date needs to be assigned. Or, if the task did not start, then the forecasted Start date needs to be rescheduled to a date later than 1/5/2018. That would resolve this invalid scenario.

Scenario 2: Invalid Task Date in the Future

Now let’s look at our second scenario. In this scenario, we have a task that has either a forecasted Start or forecasted Finish date in the future with an Actual Start or Actual Finish date in the future as well. This is also considered an invalid task since there can be no Actual Start or Actual Finish date in the future. The future has not occurred yet. To resolve this situation, change the Actual Start or Actual Finish date to a valid date in the past or delete the invalid Actual Start or Actual Finish date.

Let’s look at an example of Scenario 2. In the below schedule, we see a task that has a forecasted Start date of 1/12/2018. This task also has an Actual Start date of 1/12/2018. This Actual Start date of 1/12/2018 is in the future. It is after the Status Date of 1/5/2018. The future has not occurred yet. This is an invalid task that needs to be updated.

If the task did start, then revise the Actual Start date to a date that is on or before the Status Date. Or if the task did not start, then delete the Actual Start date.

Scenario 3: Progress Recorded Incorrectly

In our third scenario, we have a task has an Actual Start date correctly assigned, but the progress indicator does not extend to the Status Date. To resolve this scenario, correctly update the task progress by clicking ‘Mark on Track’. This will extend progress to the Status Date.

Now let’s look at an example of Scenario 3… In the below schedule, we have a task that has a valid Actual Start date on 1/1/2018 and a valid forecasted Finish date of 1/12/2018. So we have a task that has valid Start and Finish dates. So the issue is not tied to these dates. The issue is related to how the task has been updated. We see that the task has 10 days duration assigned to it. If we look at the % Complete field, we see that it says 10% and the Remaining Duration is 9 days. This would imply that we used 1 day of duration so far (10 percent of the total 10 day duration).

We also see that the dark blue task progress bar in the Gantt chart does not extend to the Status Date. This is not valid. As of the status date 1/5/2018, we should have used 50% of the total duration and should have 5 days Remaining Duration left. The dark blue task progress bar should also extend to the Status Date.

We can resolve this issue by clicking ‘Mark on Track’ and then adjusting the Remaining Duration based on task owner feedback. This is a common issue in schedules where the % Complete field is manually updated. The % Complete field should not be manually updated since this field is tied directly to duration, not the physical percent complete value of the task. If you want to track the physical complete value of the task, then use the Physical % Complete field.

Scenario 4: Completed

Let’s now look at a scenario where nothing is wrong. In this scenario, we have a task that has been completed. No action is required. If we look at the task in the below schedule, we see that the forecasted Start and forecasted Finish dates are both in the past and the task has been completed. This is a properly statused task. The Actual Start and Actual Finish dates are both in the past.

Scenario 5: Future Task

In our fifth scenario, we once again have a task that requires no action. In this situation, the task has a forecasted Start date in the future. Because the task has a forecasted Start date in the future, we do not need to take any action right now. If we look at the task in the below schedule, we see that the forecasted Start and forecasted Finish dates are both in the future. No action required.

Scenario 6: In Progress – Correctly Statused

In our sixth scenario, we once again have a task that requires no action. In this scenario, we have a task that is in progress and that has been statused correctly. Looking at the below schedule, we can see that the task has a forecasted Start date in the past with a valid Actual Start date. And it has a forecasted Finish date in the future with no Actual Finish date. We also see that the dark blue task progress bar extends up to the Status Date. Task progress has been recorded properly. No action required.

Our Statusing Fields

Now that we understand the different task statusing scenarios, now I will introduce you to two different statusing field options. One option uses a custom Text field to provide a text-based status. And the other option uses a custom Number field to provide a graphical indicator.

You may want to use both options in your schedules as they can both be used together to provide both text and graphical indicators for the most flexibility.

Option 1: Task Status Field

In this option, we will configure a custom Text field to provide a text-based task status.

Step 1: Select an available custom Text field. Make sure that you are choosing a task field, not a resource field. Rename the field as ‘Task Status’ or another suitable name of your choosing.

Step 2: Click the ‘Formula’ button.

Step 3: Paste the below formula into the formula box and click ‘OK’. Once you do this, make sure that the ‘Formula’ button is selected as shown in the previous screen capture.

IIf([Summary]=Yes,””,IIf([Status Date]>#1/1/2150#,”Status Date Not Set”,IIf(([Scheduled Start]<=[Status Date] And [Scheduled Start]<>[Actual Start]) Or ([Scheduled Finish]<=[Status Date] And [Scheduled Finish]<>[Actual Finish]),”Invalid Task Date – Forecast in the Past with no Actuals”,IIf(([Actual Start]>[Status Date] And [Scheduled Start]=[Actual Start]) Or ([Actual Finish]>[Status Date] And [Actual Finish]=[Scheduled Finish]),”Invalid Task Date – Actual in the Future”,IIf([Actual Start]<[Status Date] And ProjDateDiff([Stop],[Status Date])<>0 And [% Complete]<100,”Progress Recorded Incorrectly”,IIf([% Complete]=100 And [Actual Finish]<=[Status Date] And [Actual Finish]=[Scheduled Finish],”Completed”,IIf([Scheduled Start]>[Status Date] And [Scheduled Start]>[Status Date],”Future Task”,”In Progress – Correctly Statused”)))))))

Step 4: Click the ‘Use Formula’ button in the ‘Calculations for task and summary group rows’ section.

Step 5: Click ‘OK’ to close the Custom Fields definition window. The field is now ready to be added to a table.

Option 2: Task Status Indicator Field

In this option, we will configure a custom Number field to provide a graphical task status indicator.

Step 1: Select an available custom Number field. Make sure that you are choosing a task field, not a resource field. Rename the field as ‘Task Status Indicator’ or another suitable name of your choosing.

Step 2: Click the ‘Formula’ button.

Step 3: Paste the below formula into the formula box and click ‘OK’. Once you do this, make sure that the ‘Formula’ button is selected as shown in the previous screen capture.

IIf([Summary]=Yes,0,IIf([Scheduled Start]<=[Status Date] And [Scheduled Start]<>[Actual Start],1,IIf([Scheduled Finish]<=[Status Date] And [Scheduled Finish]<>[Actual Finish],1,IIf([Actual Start]>[Status Date] And [Scheduled Start]=[Actual Start],1,IIf([Actual Finish]>[Status Date] And [Actual Finish]=[Scheduled Finish],1,IIf([Actual Start]<[Status Date] And ProjDateDiff([Stop],[Status Date])<>0 And [% Complete]<100,1,IIf([% Complete]=100 And [Actual Finish]<=[Status Date],2,IIf([Scheduled Start]>[Status Date] And [Scheduled Start]>[Status Date],0,3))))))))

Step 4: Click the ‘Use Formula’ button in the ‘Calculations for task and summary group rows’ section.

Step 5: Click the ‘Graphical Indicators…’ button.

Step 6: Configure the Graphical Indicator as shown below. Then click ‘OK’.

Step 7: Click ‘OK’ to close the Custom Fields definition window. The field is now ready to be added to a table.

This custom Number field will then show the following indicators:

  • Red Dot: Tasks with invalid task dates and incorrectly statused tasks
  • Green Dot: In progress tasks that are correctly statused
  • Check Mark: Completed tasks
  • No indicator: Future tasks

Our Final Product

Now that we have completed configuring both custom fields, let’s add them to our schedule. Below is what we would see for each of our six scenarios that we discussed in this tutorial. Remember, the Status Date must be set…

We can see that our two custom fields are now giving us some very valuable information. We can now quickly and easily see which tasks have statusing issues and which do not. Additionally, we can apply an AutoFilter to either of our newly added custom fields to focus our attention on specific scenarios to improve the health of our schedule.

Summary

In this tutorial, you learned how to create two different custom fields to help us properly status our project schedule. The goal is to get the most accurate forecasts out of our project schedules. We can only do that if our schedules are statused properly. Even one incorrectly statused task can cause our schedule to produce inaccurate forecasts. So use these fields to get the most accurate forecasts out of your schedules!