One benefit of writing this TechRepublic column is being able to interact with the readers and help them solve project management problems. I recently received a request from a TechRepublic reader to build a custom filter that identifies all the tasks within a four-week window. The four-week window is used to identify all the tasks that are three weeks ahead of a status date and all the tasks that are one week behind the status date. The project manager wanted a quick and easy way to identify tasks within the reporting window and only wanted to specify a single input date.

I also thought this filter would be useful as a Microsoft Project 2010 Highlight, so project managers could see the date range within the context of the entire project (Figure A). The highlight and filter approach makes it easier for the project team to focus on the immediate and future tasks.
Figure A

Example for a four-week date range based on status date. (Click the image to enlarge.)

Initially, I thought this would be a simple filter on the start or finish dates based on the project status date. Since Microsoft Project filters can only use task or resource level fields, I couldn’t easily filter on the project status date field since it is a project level field. Without resorting to VB programming, the only solution was to develop a complex formula in a custom field and then filter on the field values.

The four-week window is calculated using two start and finish date ranges:

  • Project Status date – 1 week >= Start Date <= Status date + 3 weeks
  • Project Status date – 1 week >= Finish Date <= Status date + 3 weeks

If a task start or finish date is within the date range, it will be displayed in the Microsoft Project filter. The following steps create the custom field logic and the filter.

Step 1: Set the project status date

  1. Go to Project | Status date.
  2. Select the proper status date.
  3. Click OK.

Step 2:  Customize the Flag1 field

  1. Go to Project | Customs Fields.
  2. Click the Flag1 field and rename the field 3-1 Flag.
  3. Under Custom Attributes, click Formula.
  4. Enter the following formula (Figure B):

    5. IIf(([Start]>=DateAdd(“ww”,-1,[Status Date]) And [Start]<=DateAdd(“ww”,3,[Status Date])) Or ([Finish]>=DateAdd(“ww”,-1,[Status Date]) And [Finish]<=DateAdd(“ww”,3,[Status Date])),Yes,No)

    The formula is a complex IF statement that adds and subtracts weeks from the project status date to establish a date range. The DateAdd function adds three workweeks to the project status date and is also used to go back in history one week from the project status date.

  5. Click OK.

Figure B

Custom flag formula (Click the image to enlarge.)

If you want to see each task’s calculation, select the Insert The 3-1 Flag Into The Table. If you don’t see a Yes or No indicator, go to the Project tab and click the Status Date button set in the navigation bar.

Step 3:  Create a 3-1 filter

  1. Go to View | Filter | New Filter.
  2. Enter 3-1 Window in the Name field (Figure C).
  3. Click the 3-1 Flag field name.
  4. Set the Test to equals.
  5. Set the Value to Yes.
  6. Click Save.
  7. Click Close.

Figure C

3-1 Window (Click the image to enlarge.)

Step 4: Apply the 3-1 Window as a filter or highlight to the project schedule

  1. Go to View | Filter.
  2. Click the 3-1 Window filter.
  3. Since the project status date was set, the filter will display all the tasks that meet the criteria (Figure D).

Figure D

3-1 Window filter applied (Click the image to enlarge.)

In actual practice, I find it useful to apply the filter as a Highlight. Viewing the date range window among the other tasks provides better context for the work being performed. If you select the 3-1 Window in the Highlight drop-down menu, the highlight window will appear within the other tasks as in Figure A.

Key points to remember

If you apply custom filters to your project, remember Microsoft Project filters can only evaluate task or resource level data. If you need to evaluate a project level field, you need to create a custom formula in a custom field. Once the custom formula provides a final calculation, the task level filter can evaluate it.

The filter can be further improved by modifying it to evaluate incomplete tasks or by applying the task range window to the project baseline dates. A custom report can also be developed using the 3-1 Window as the underlying task filter in the Microsoft Project Reporting menu.

Do you have a Microsoft Project customization request?

If you have a Microsoft Project customization request or just a question on how to apply custom formulas and filters, please email me at andy at tactical project management dot com.  Your request might appear as a tutorial in this blog!