« Excel Keyboard Shortcuts for date and time | Main | Management Theory by Google »

MS Project Tip - Creating User-defined Filters

One of the most common activities with large schedule files is filtering them down to show the most important tasks. In most cases, important means immediate and many industries (the construction industry for example) a 3-week look ahead schedule showing current and upcoming activities is very common.

Project's built-in filters allow you to filter based on a date range, but the date range filter asks for user input and you need to select the time periods. There is no way to easily automate this in VBA short of recreating and applying the filter, but in the more recent versions of project there is a very simple way to do this by using a custom field formula.

I've described how to set up custom field formulas before. (Please click the link and read about them if you are new to the topic). For filtering purposes, the simplest way is to use a Flag field. Flag fields have a value of either "Yes" or "No". You can set them manually, but here we are going to use a formula. The formula I am showing performs a few simple tests. First it checks for all tasks that begin before a date three weeks in the future. Then it removes any which are already complete. Of these tasks it selects all of those which are not finished yet. If the task meets those criteria, the field is marked "Yes". If it doesn't then the field remains as "No". Here is the formula:

IIf(([Start]<(Now()+21) And ([% Complete]<100)) And ([Finish]>Now()),"Yes","No")

If you paste this formula into a flag field (for example Flag1) then all you need to do is create a filter which shows any tasks for which Flag1 is "Yes". Note that the formula as written doesn't show any incomplete tasks which are supposed to be completed in the past. Theoretically if you have an updated schedule you won't have any tasks like that, but you can modify the logic to add that condition. Using this technique you should be able to get around almost any interactive filtering challenge.

RELATED POSTS
  • Microsoft Project Tip - Formulas and the IIF statement
  • New Fields in Microsoft Project 2010
  • Project 2010 Tips and Tricks Webcast
  • Date Math - DateAdd, ProjDateAdd in Microsoft Project
  • Views and Tables in Microsoft Project
  • Project Server 2010 Technical Requirements
  • Project Server OLAP Views Errors
  • Microsoft Project Undo Levels and Macros
  • Making the move from VBA to VSTO in Microsoft Project
  • Analyze Microsoft Project Resource Usage Data In Excel

  • Comments (3)

    Robert A. Dudley:

    Very nice. I am new to VB and this is a great tool. I don't like the built-in date range filter because it does not do a nice clean job. Thanks,
    Rob Dudley
    P.S. do you have one that will look for items with-in a date range?

    Rob, I guess it depends what you mean by "clean job". You can add additional criteria to exclude tasks which start before or end after the date range or practically anything you want. The first step is defining what you want. Write it down. Then derive the filter criteria which you need to achieve it. Some times it takes combining a few criteria to do it. Just enter each on a different line and set the "and" and "or" correctly. I can't be more specific unless you tell me exactly what you want -Jack

    Seven:

    Hi,

    I am looking for a way to do a weekly baseline exception filter.

    Specificially, I want to show a table presenting baseline tasks that were planned to start or finish during the week that didn't.

    Would greatly appriciate any advise.

    Thx

    Seven

    Bryan Eaton:

    Im using 2010 and have set up a user defined duration field I have named "Delta Start" set to "ProjDateDiff([Current Date],[Scheduled Start])". This gives the difference between the current date and the start of the activity - if it is positive it is in the future, negative it is in the past. then my filter is [Delta Start]

    Post a comment

    (Comments are moderated to fight SPAM and will be published after I have a chance to approve them. Thanks for waiting.)

    About

    The previous article is Excel Keyboard Shortcuts for date and time.

    The next article is Management Theory by Google.

    Current articles are in the main index page and you can find a complete list of articles in the archives.

    Creative Commons License
    This weblog is licensed under a Creative Commons License.
    Powered by
    Movable Type 3.34