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.
Comments (3)
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
Posted by Robert A. Dudley | April 16, 2007 11:25 AM
Posted on April 16, 2007 11:25
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
Posted by Seven | July 25, 2007 10:20 AM
Posted on July 25, 2007 10:20
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]
Posted by Bryan Eaton | January 18, 2012 2:21 PM
Posted on January 18, 2012 14:21