Guild of Project Controls: Compendium | Roles | Assessment | Certifications | Membership

Tips on using this forum..

(1) Explain your problem, don't simply post "This isn't working". What were you doing when you faced the problem? What have you tried to resolve - did you look for a solution using "Search" ? Has it happened just once or several times?

(2) It's also good to get feedback when a solution is found, return to the original post to explain how it was resolved so that more people can also use the results.

Creating a look-ahead filter in MS Project 2010

8 replies [Last post]
Thu, 2013-06-06 07:38
Danya Pearce
User offline. Last seen 3 years 6 weeks ago. Offline
Joined: 15 Jul 2004
Posts: 70
Groups: Building Information Modelling, Women in Planning & Scheduling (WiPS)

Hi ALl

 

Can someone give me an idea how to create a filter that i can save to do a 4 week look ahead instead of the date range filter?

 

Thanks

 

Moi

Top

Replies

Tue, 2015-09-08 14:03
#1
John Lewis
User offline. Last seen 8 years 32 weeks ago. Offline
Joined: 23 Jul 2015
Posts: 3
Groups: None

@Greg,

I think you removed a necessary bracket - presumably the one before the last "or" -- please re-add it back. Here is the full formula -- make sure you capture both of the first two characters -- both left parenthesesis characters.

(( [Start]>DateAdd("d",-21,now())Or [Finish]>DateAdd("d",-21,now()))And( [Start]<DateAdd("d",21,now())Or[Finish]<DateAdd("d",21,now())))Or ( [Finish]<DateAdd("d",21,now())And[% Complete]<100) HTHJohn
Top
Wed, 2015-09-02 23:11
#2
Greg Young
User offline. Last seen 6 years 24 weeks ago. Offline
Joined: 30 Aug 2015
Posts: 6
Groups: None

John

 

I used your formula for a lookahead (changed to 21 days) but when I pasted it into a flag feild MSP reported a error in syntax. I deleted the offending bracket and MSP accepted the alteration so I ended up with;

([Start]>DateAdd("d",-21,now()) Or [Finish]>DateAdd("d",-21,now())) And ([Start]<DateAdd("d",21,now()) Or [Finish]<DateAdd("d",21,now())) Or ([Finish]<DateAdd("d",21,now()) And [% Complete]<100).

 

It works fine except it now includes any task 100% completed. I can of course exclude these using the filter but obviously there is something wrong with my version of the formula.

Can you help please.

 

Cheers

Greg

Top
Thu, 2015-07-23 15:03
#3
John Lewis
User offline. Last seen 8 years 32 weeks ago. Offline
Joined: 23 Jul 2015
Posts: 3
Groups: None

Enjoyed this thread.

...Though I'm a little late getting to it...

Evgeny's comment is spot-on. Using this approach, I came up with this formula to capture all "active" tasks -- i.e. tasks that don't end before the earliest date of the window AND tasks that don't start after the latest date of the window.

(
 (
  [Start] > DateAdd("d",-7,now())
  or
  [Finish] > DateAdd("d",-7,now())
 )
 and
 (
  [Start] < DateAdd("d",7,now())
  or
  [Finish] < DateAdd("d",7,now())
 )
) The window is hard-coded to 7 days in this formula - just change the "7"s to whatever you want the window to be. This formula can be pasted into the Custom Field > Formula window for any Flag field in MS Project. It doesn't require any other field.  In addition to the active tasks, I wanted to show tasks that will be due before the end of the window and are incomplete, so updated the formula to be this: (
 (
  [Start] > DateAdd("d",-7,now())
  or
  [Finish] > DateAdd("d",-7,now())
 )
 and
 (
  [Start] < DateAdd("d",7,now())
  or
  [Finish] < DateAdd("d",7,now())
 )
)
or
(
 [Finish] < DateAdd("d",7,now())
and
[% Complete] < 100
)  Hope this helps!
Top
Tue, 2013-06-11 16:05
#4
Rafael Davila
User offline. Last seen 12 hours 16 min ago. Offline
Joined: 1 Mar 2004
Posts: 5229
Groups: Spider Project Team

Evgeny,

Some time ago I created a filter for Spider with an approach similar to yours and it worked with a reduction on the number of conditional statements. I agree there are other ways just not as obvious.

If instead of looking at what shall be included we look at what shall not the rules become; activities that finish before the range as well as activities that start after the range shall not be included. In this particular case looking for exclusions is easier.

photo 2wLAH_zps1ec28b8a.jpg

Best Regards,

Rafael.

Smiley Faces

Top
Tue, 2013-06-11 08:50
#5
Evgeny Z.
User offline. Last seen 38 weeks 6 days ago. Offline
Joined: 13 Jan 2008
Posts: 442
Groups: None

Rafael,

I agree with your definition of a look ahead filter.

But all your 8 rules (4 creterias, 2 rules each) can simply be replaced by 2 rules, if you create a filter for tasks, which shall NOT be included in the look ahead filter and then inverse the selection.

This is what I did.

Regards.

Evgeny.

Top
Tue, 2013-06-11 00:53
#6
Rafael Davila
User offline. Last seen 12 hours 16 min ago. Offline
Joined: 1 Mar 2004
Posts: 5229
Groups: Spider Project Team

Look ahead starting after DD can be issued but it is common to update the schedule when today () is over DD as it might already be obsolete and some activities not progressing as expected might be left out.  If no update is to be performed yet in such case it would be better to issue a longer range look ahead always starting from DD.

look ahead start date = usually DD for a 2 week look ahead

look ahead finish date = usually DD+14

A 2 week look ahead shall look for:

  1. Activity Start less than look ahead start date, and Activity Finish equal or greater than look ahead start date and less than or equal to look ahead finish date [Activity starts before look ahead range and finishes within range]. OR
  2. Activity Start equal or greater than look ahead start date and equal or less than lookahead finish date and Activity Finish greater than look ahead finish [Activity start within look ahead range but finishes after range]. OR
  3. Activity Start equal or greater than look ahead start date and Activity Finish equal or less than look ahead finish date [Activity starts and finishes within lookahead range]. OR
  4. Activity Start less than look ahead start date and Activity Finish greater than look ahead finish date [Activity starts before range and finishes after range].

photo LOOKAHEADRANGE_zps3f68a2d9.jpg

Best Regards, 

Rafael

Top
Mon, 2013-06-10 20:47
#7
Evgeny Z.
User offline. Last seen 38 weeks 6 days ago. Offline
Joined: 13 Jan 2008
Posts: 442
Groups: None

Herman,

see below the formula. If you assign it to custom flag (e.g. flag1), and filter all tasks with "Yes" value, you will get all the tasks, which have some presence +-14 days from current time. By changing the value you subtract and add to Now() you can adjust the range you are selecting

 

IIf(([Finish]<(Now()-14)) Or ([Start]>(Now()+14));  No;  Yes)

So, for 4 weeks look ahead it will look like this

IIf(([Finish]<(Now())) Or ([Start]>(Now()+28));  No;  Yes)

 

Hope this helps.

 

Evgeny

Top
Thu, 2013-06-06 17:28
#8
Evgeny Z.
User offline. Last seen 38 weeks 6 days ago. Offline
Joined: 13 Jan 2008
Posts: 442
Groups: None

Herman,

I would create a custom field type Flag, which value would be calculated by a formula, crafted in a way, that it would produce a "Yes" value for the tasks which meet your criteria.

When crafting this formula you need to use Now() function, to to make sure it is always based on the current date.

Then you will just have to filter tasks by this flag.

If such formula gets too complex, you can create a Macro in visual basic, which would walk through every task and set the custome flag based on your creterias. In this case you will have to run this Macro every time you want to use this filter  after changes to project.

Regards.

Evgeny

Top