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.
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.
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!
Member for
21 years 8 months
Member for21 years8 months
Submitted by Rafael Davila on Tue, 2013-06-11 17:05
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.
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.
Member for
21 years 8 months
Member for21 years8 months
Submitted by Rafael Davila on Tue, 2013-06-11 01:53
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:
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
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
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
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].
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)
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.
Member for
10 years 3 months@Greg,I think you removed a
@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.
Member for
10 years 2 monthsJohn I used your formula for
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
Member for
10 years 3 monthsEnjoyed this thread....Though
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())
)
)
(
[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
)
Member for
21 years 8 monthsEvgeny,Some time ago I
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.
Best Regards,
Rafael.
Member for
17 years 9 monthsRafael,I agree with your
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.
Member for
21 years 8 monthsLook ahead starting after DD
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:
Best Regards,
Rafael
Member for
17 years 9 monthsHerman,see below the formula.
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
Member for
17 years 9 monthsHerman,I would create a
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