Author: Dave Gordon

Dave Gordon is a project management consultant with over twenty five years of experience in implementing human capital management and payroll systems, including SaaS solutions like Workday and premises-based ERP solutions like PeopleSoft and ADP Enterprise. He has an MS in IT with a concentration in project management, and a BS in Business. He also holds the project management professional (PMP) designation, as well as professional designations in human resources and in benefits administration. In addition to his articles and blog posts, Dave curates a weekly roundup of articles on project management at The Practicing IT Project Manager, and he has authored or contributed to several books on project management.

Add a Calculated Status Indicator to Detail Tasks

While Project includes a Status field, it’s not always exactly what I want. I use a numeric field to calculate the status of detail tasks in the project plan, and display stoplights—Green, Amber, and Red—based on the calculated value. I’ve found it adds some immediacy to project status meetings. Note that this formula produces reliable results for detail tasks, but summary tasks can have some rather complex roll-ups, so I don’t apply it to them. Here’s what this status field does: For zero duration tasks (milestones) If % Complete = 100, return Blue If the scheduled finish is at least seven days away, return Clear Otherwise, if % Complete is < 80, return Red If % Complete is between 80 and 99, return Amber For non-zero duration tasks If % Complete = 100, return Blue If it’s not scheduled to start yet, return Clear If past the scheduled finish date and % Complete is less than 100%, return Red If % complete >= the prorated expected completion, return Green Otherwise, return Amber. That “prorated expected completion” is calculated using the project calendar, so it takes non-working time like holidays into account. If the percent complete is less than the number of days since the task was scheduled to begin, divided by the task duration, it will be Amber; otherwise, it will be Green. Adding the Stoplight Indicator To add this to your project schedule, right click on the column where you want to insert the status indicator. Click “Insert Column” and then select Number 1. Right click on the newly inserted column and select “Custom Fields.” From here, you can rename the field (I chose “St” to keep it brief). Under custom attributes, click on the radio button for Formula. You’ll get a warning message, and that’s fine because you really do want to calculate the values. Then click on the Formula button, and paste in the following formula: IIf(Duration=0,(IIf([% Complete]=100,5,IIf(Finish<[Status Date]+7 And [% Complete]<100 And [% Complete]>=80,3,IIf(Finish<[Status Date]+7 And [% Complete]<80,4,1)))),IIf([% Complete]=100,5,IIf([% Complete]>=100*(Abs(ProjDateDiff(Start,[Status Date])/ProjDateDiff(Start,Finish))),2,(IIf(Finish>[Status Date],IIf(Start>[Status Date],1,3),4))))) Note the brackets around the fields “% Complete” and “Status Date.” This is because of the embedded space in the field name. Click OK. Now click on the Graphical Indicators button. This is where you choose the colors to display. In each row, under “Test for” select “equals.” Associate the values in the second field with the images in the third field, like this: 5 Blue Completed 4 Red Past finish date 3 Amber Behind expected progress 2 Green Progressing on track 1 Clear Future task Note that you only want these indicators to appear for non-summary rows, since the % Complete field isn’t accurate for summary rows. Click OK. Using the Stoplight Indicator In the formula, we reference a field called Status Date. This is set at the project level, under the Project tab. In this case, I’ve set the status date to May 21. The formula will compare the status date to the Start and Finish dates for each task, and based on % Complete, determine whether a task scheduled to be “in progress” is in Red, Amber, or Green status. Consider this example: The first two tasks are Blue because they are 100% complete. Task 3 is Red because it’s two days past the finish date and it’s only 75% complete. Task 4 is Green because two days have passed, the task was scheduled for three days, and it’s 75% complete. If the task were less than 67%, it would be Amber. Task 5, a milestone, is Amber because it’s only 80% complete, and near the Finish date. All the subsequent tasks have Start dates after the Status date, so they have a clear indicator. Remember that “days” is defined using the project calendar, so it only uses working days. Final Thoughts As you can see, this is a rather complex formula. The calculations will only be meaningful if the % Complete information it’s based on is maintained on a consistent basis. Set some standards for the values you enter. For example: Milestone tasks: Enter either 0% or 100%, depending on whether all predecessor tasks are complete. Alternatively, enter 0%, 25%, 50%, 75%, and 100% as predecessor tasks are completed. Detail tasks: Enter 0%, 25%, 50%, 75%, and 100%, as the task is worked. Alternatively, enter other values, appropriate to the task and the performing team members. Whatever standard you choose, communicate it to the project team and use it consistently. Don’t fall into the trap of starting a task at 90% and then spending the next few weeks waiting for the performer to report 100%! Define “inch pebbles” that can be reported at the task level. If necessary, break the task into multiple tasks so that the durations are in a smaller, more meaningful range. A consistent granularity is the key to measuring and reporting progress. If you think of other variations that might be useful, such as a simpler version of the prorated expected completion calculation, leave a comment below. Related Content Webinars (watch for free now!): Advanced Tips for Resolving Resource Over/Underallocation Eliminate the Confusion – Deep Dive into Task Types and Effort Driven Articles: Exploratory vs. Explanatory Visuals in Planning Microsoft Project Do’s and Don’ts: Enter Task Durations When a Fixed Duration Task is Truly Not “Fixed Duration”

Adding a Current Tasks Flag

It is important to keep your project team focused on current and near-term tasks. If you want to send out a weekly update to your project team, showing which tasks are active, but incomplete, or scheduled to start in the next week, it would help to have a flag that automatically identifies those tasks for you. Here’s how to add it to your MS Project plan. Inserting the Flag Right click on the column where you want to insert the status indicator. Click “Insert Column” and then select Flag 1. Right click on the newly inserted column and select “Custom Fields.” From here, you can rename the field (I chose “Act” to keep it brief). Then, under custom attributes, click on the radio button for Formula. You’ll get a warning message, but that’s fine because you really do want to calculate the values. Next, click on the Formula button and paste in the following: IIf([Start]<ProjDateAdd([Status Date],”5″) And [% Complete]<>100,True,False) Click OK, and then click on the Graphical Indicators button. This is where you choose the flag to display and associate it with a specific value. The formula will return a binary value—think of it as Yes or No. In this example, the flag will only appear for non-summary rows. Your project team members will likely be more interested in the specific tasks assigned to them than in the summary tasks. Click in the first cell in the first row and select “equals” from the pulldown list. Then, click within the second cell. You will be presented with a list of all available fields, but you want the “Yes” value at the top of the list. Click in the third cell and choose a graphical image that meets your needs. I used a blue flag, but the light bulb image is also a good choice. To confirm, click OK. Using the Flag In the formula, we reference a field called Status Date. This is set at the project level, under the Project tab. In this case, I’ve set the status date to May 14. The formula will add five working days to the status date, as reflected in the project calendar. In this case, the result will be May 21. If a non-working day, such as a holiday, falls during that period the formula will take that into account. Thus, any task with a start date on or before May 21 and not at 100% complete will be flagged. In our example, Task 1 is not flagged because it is marked 100% complete. Task 2 was scheduled to finish on May 11, but is not yet 100% complete, so it is flagged. Task 3 and 4 are flagged because the start dates fall on or before May 21. Tasks that are scheduled to start after May 21 are not flagged. By setting the filter on the Act field to Yes, only the flagged tasks will be visible. The resulting data can easily be captured in a PDF or image file and incorporated into an Email or status report. This is useful for communicating current and near-term tasks to the project team, as noted earlier. Modifications The “5” in the formula is the part that specifies five working days. For two weeks, use “10”. If you want to use a different reference point, change Start to a different date field such as Baseline Start. Explore the list of available fields in the Formula screen by clicking on the Field button. Another alternative is to use the current date. In the formula, replace [Status Date] with Now(). This will save the step of setting the status date. IIf([Start]<ProjDateAdd(Now(),”5″) And [% Complete]<>100,True,False) You can also add criteria to the formula. For example, you may want to filter out summary tasks. This allows you to focus attention on the individual tasks. IIf([Start]<ProjDateAdd([Status Date],”5″) And [% Complete]<>100 And [Summary]=False,True,False) If you think of other variations that might be useful, leave a comment below. Related Content Webinars (watch for free now!): Capturing Lessons Learned Information – Making your current and future project smarter! Webinar: How to Merge Two Departments into One Project Server/Online Instance Articles: 7 Incorrect Ways to Use Microsoft Project: Using Predecessors in Summary Tasks 3 Incorrect Ways to Do Scheduling with Microsoft Project 3 Correct Ways to Do Great Scheduling with Microsoft Project

Add a Cutover Weekend Calendar

Some projects are just disruptive. This is especially the case when you are replacing an old technology with a new one. There usually isn’t a “good” time to move from one enterprise solution to another, so many IT project teams schedule the transition over a weekend. Bad for the people doing the work, but better than shutting everyone down for two days in the middle of the work week. The challenge is to designate the cutover Saturday and Sunday as working days for the tasks of interest, while ensuring that other tasks aren’t shifted in time. The solution is a custom calendar, applied to only the intended tasks. The following article will show you how to do just that. Creating a Custom Calendar Begin by navigating to the View tab in Microsoft Project. Click on Change Working Time, then click on the button labeled Create New Calendar. We have already entered our organization’s holidays as exceptions to the Standard calendar, so let’s make a copy of it. We can rename it Cutover Weekend. Click OK. Next, we can add two more exception days. To do this, scroll to the bottom of the exception list, and enter the planned exceptions. Then click OK. For each newly entered exception day, click on the Details button. Change the radio button to Working times. The default working hours for the project will appear. To accept these changes, click OK. Assigning the Cutover Calendar to Selected Tasks Now that we have a separate calendar that makes cutover weekend working days, let’s assign it to the cutover tasks. Right-click on the task and select Information. Then navigate to the Advanced tab. Click on the pull-down list in the Calendar field and select the new custom calendar, Cutover Weekend. Then click on the check box, Scheduling ignores resource calendars. Click OK. Now if the Schedule Mode is set to Auto Scheduled for the task, the dates will recalculate to reflect the weekend days. Other Applications As you can see, assigning a custom calendar in this way isolates the effect to just the tasks to which the calendar applies. In addition to this example, you can also apply the same approach to situations where a portion of the project team will be unavailable during the normal work week. One potential application would be for when certain members of the team will be occupied in other activities, such as financial year-end processing. Note that we’ve created yet another custom calendar. In this case, we’ve set May 14 through May 25 as a single exception, all non-working days. Now we can assign the calendar to a Resource. To do this, bring up the Resource Sheet view, right-click on the resource affected, and select Resource information. Now click on Change Working Time. In the Base Calendar field, select the new Year End Processing calendar from the pull-down list. Click OK. When you return to the Resource Information window, click OK again. Now, tasks assigned to this resource will be scheduled with all identified non-working days accounted for, including both the base holidays and the year-end processing period. This approach is especially valuable for situations where several members of the team will be affected by an external event. If you only have one team member impacted, you could also enter exceptions in the Resource’s Change Working Time screen, as you did in the Project Calendar. In Closing These simple techniques will allow you to create and maintain your project schedule around your team’s availability. An important reminder: keep track of your custom calendars—they need maintenance, especially if you reschedule your cutover! Related Content Webinars (watch for free now!): Task Planning using Microsoft Project What’s the value of Schedule Risk Analysis? Articles: Levels of Project Scheduling Proficiency Are You Using the Team Planner View Feature in Microsoft Project? Resource Leveling: Scheduling vs. Leveling

How to Add Your Organization’s Holiday Schedule to MS Project

As the end of each year approaches, it’s important to look at your project plans that extend into the coming year.  Have you accounted for all of the holidays your project will observe? As I recently pointed out to another project manager, there aren’t four work weeks in either November or December.  Fortunately, if you’re using Microsoft Project, you can define non-working days in order to prevent the embarrassment of a deliverable coming due on Thanksgiving Day.  Identifying Non-Working Days in the Standard Project Calendar On the Project tab, select Change Working Time.  Note that the calendar displayed in the example is the Standard (Project Calendar). This is the one used for all tasks and resources, by default. You can then enter holidays under the Exceptions tab.  Note that Exception days appear in the calendar in blue and scheduled non-working days appear in gray. Type in the name of the exception and then select the Start date from the pop-up calendar window. The Finish date will default to the same day as the Start date. Creating an Alternate Calendar If your project includes team members in another country, you can create a separate calendar for them. Simply click on the Create New Calendar button. In addition to creating a unique name, you can “inherit” the defined non-working days from another calendar. Then you can simply delete the holidays that don’t apply, create new ones, and save it. You’ll then have two (or as many as you need) calendars to use within your project. Assigning Calendars to Resources and Tasks Once you have defined your calendars, you can associate the proper calendar with each resource. This will cause the scheduling system to respect days off based on the calendar of the resources assigned to each task. In addition, you can create special calendars for specific tasks like a cutover weekend. To do this, make an exception of a scheduled non-working day, so that it appears to be a working day.  Use this feature carefully – having some of the team working over a weekend can easily throw off the schedule for the entire team. You can also tell the scheduling system to ignore the Resource Calendar, but do so very carefully! Finding Holiday Dates for Your Project Schedule Your organization probably publishes a holiday calendar for the coming year sometime in the fourth quarter, but if you are working with project team members from outside your organization, it can be difficult to determine what holidays apply and what dates they fall on, especially if some of them are located off-shore. I publish a list of commonly observed national and religious holidays each year. Look for the next one in early November at The Practicing IT Project Manager. Related Content Webinars (watch for free now!): Task Planning using Microsoft Project What’s the value of Schedule Risk Analysis? Articles: Levels of Project Scheduling Proficiency Are You Using the Team Planner View Feature in Microsoft Project? Resource Leveling: Scheduling vs. Leveling