Setting up a dynamic date filter

[Zel Hilliar]

I'm trying to setup a dynamic date filter on a certain view to make it easier
for the business analysts on this project to see their tasks which are in the
past but have not yet been completed (set to 100%) in order to keep our
project plan clean, tasks currently sitting at over 3000.

On the filter I would like to set the finish date to display tasks finishing
2 days before the current date, is there a way of doing this?

Any help would be much appreciated.

thanks,
Zel

 

[John]

I'm trying to setup a dynamic date filter on a certain view to make it easier
for the business analysts on this project to see their tasks which are in the
past but have not yet been completed (set to 100%) in order to keep our
project plan clean, tasks currently sitting at over 3000.

On the filter I would like to set the finish date to display tasks finishing
2 days before the current date, is there a way of doing this?

Any help would be much appreciated.

thanks,
Zel

Zel,
Probably your best approach is to use an interactive filter. You mention
that you want to see tasks that finish 2 days before the current date.
Technically that is impossible - time machines haven't been invented yet
although there is some very fascinating work on worm holes that is well
documented on SciFi's Stargate SG-1 program.

Alright, I'll be serious. Although you do not know when tasks will
actually finish you can show all tasks with an estimated Finish date of
2 days prior to the current date with the following filter:
Field Name Test Values
Finish is less than or equal to "enter date 2 days prior to
today"?

Hope this helps.
John
Project MVP

 

[Zel Hilliar]

Hi John,

Thanks for the reply, however I always want the filter to run on what todays
date is, so for example if I open up the project in that specific view today,
it'll show me all tasks that have finish dates on or before the 7th Dec. And
if I opened it up next Wed the 14th December and select that view, it would
automatically show me all tasks with finish dates on or before the 12th
December.. etc..

Thanks,
Zel

 

[John]

Hi John,

Thanks for the reply, however I always want the filter to run on what todays
date is, so for example if I open up the project in that specific view today,
it'll show me all tasks that have finish dates on or before the 7th Dec. And
if I opened it up next Wed the 14th December and select that view, it would
automatically show me all tasks with finish dates on or before the 12th
December.. etc..

Thanks,
Zel

Zel,
Ok, I think I understand now. You would like the filter to be
automatically applied on file open, correct? There are various ways to
do that, some easy and some more advanced.

Let's start with the easy. This method won't apply the filter
automatically but it will provide a quick convenient way to apply the
filter once the file is open. You could of course just set up the filter
as I suggested and go to Project/Filtered For and apply your filter
immediately after opening the file. You could also add the filter
selection box to a toolbar (I have mine on a customized "Formatting"
toolbar along with the view selection box and the view table selection
box). You could also set up an autofilter and set a custom filter for
the Finish field. It could test for a formula based date in a spare date
field. For example the Date1 field could be customized with the formula:
ProjDateSub(now(),"2d")

A little more advanced approach will give you exactly (what I assume)
you want but it will require the use of VBA. If you want to try it, do
the following:
1. Open the file you want to have filtered
2. Go to Tools/Macros/Visual Basic Editor
3. Hit the Project Explorer icon
4. In the Explorer pane on the left look for "VBAProject (your file name)
5. Open the folder (if necessary) until you see "ThisProject (your file
name). Double click on that icon - it will open the code pane on the
right.
6. Paste the following code in the code pane:
Private Sub Project_Open(ByVal pj As MSProject.Project)
Dim TwoDaysAgo As Date
TwoDaysAgo = DateSubtract(Now, "2d")
FilterEdit Name:="ShortList", taskfilter:=True, Create:=True, _
OverwriteExisting:=True, FieldName:="finish", _
test:="is less than or equal to", Value:=TwoDaysAgo, _
ShowInMenu:=False, showsummarytasks:=False
FilterApply Name:="ShortList"
End Sub
7. Go to File/Save [your file name]. That will save the "auto-open" code
with your file.
8. Whenever anyone opens the file, the filter will be applied. Note:
depending on your macro security setting, (Tools/Macro/Security) you may
or may not get a warning message about enabling macros when the file is
opened.

Hope this helps.
John
Project MVP

 

[Catfish Hunter]

Zel, I posted a question much like yours today. The filter I use for
uncompleted task that should complete in the next few days looks like this:
Start------- Is Less Than or Equal To------- "And before:"?
And % Comp---Is Less Than-----------------------100%

When you run the filter it ask you for a date. Try it.

Hi John,

Thanks for the reply, however I always want the filter to run on what todays
date is, so for example if I open up the project in that specific view today,
it'll show me all tasks that have finish dates on or before the 7th Dec. And
if I opened it up next Wed the 14th December and select that view, it would
automatically show me all tasks with finish dates on or before the 12th
December.. etc..

Thanks,
Zel

Zel,
Ok, I think I understand now. You would like the filter to be
automatically applied on file open, correct? There are various ways to
do that, some easy and some more advanced.

Let's start with the easy. This method won't apply the filter
automatically but it will provide a quick convenient way to apply the
filter once the file is open. You could of course just set up the filter
as I suggested and go to Project/Filtered For and apply your filter
immediately after opening the file. You could also add the filter
selection box to a toolbar (I have mine on a customized "Formatting"
toolbar along with the view selection box and the view table selection
box). You could also set up an autofilter and set a custom filter for
the Finish field. It could test for a formula based date in a spare date
field. For example the Date1 field could be customized with the formula:
ProjDateSub(now(),"2d")

A little more advanced approach will give you exactly (what I assume)
you want but it will require the use of VBA. If you want to try it, do
the following:
1. Open the file you want to have filtered
2. Go to Tools/Macros/Visual Basic Editor
3. Hit the Project Explorer icon
4. In the Explorer pane on the left look for "VBAProject (your file name)
5. Open the folder (if necessary) until you see "ThisProject (your file
name). Double click on that icon - it will open the code pane on the
right.
6. Paste the following code in the code pane:
Private Sub Project_Open(ByVal pj As MSProject.Project)
Dim TwoDaysAgo As Date
TwoDaysAgo = DateSubtract(Now, "2d")
FilterEdit Name:="ShortList", taskfilter:=True, Create:=True, _
OverwriteExisting:=True, FieldName:="finish", _
test:="is less than or equal to", Value:=TwoDaysAgo, _
ShowInMenu:=False, showsummarytasks:=False
FilterApply Name:="ShortList"
End Sub
7. Go to File/Save [your file name]. That will save the "auto-open" code
with your file.
8. Whenever anyone opens the file, the filter will be applied. Note:
depending on your macro security setting, (Tools/Macro/Security) you may
or may not get a warning message about enabling macros when the file is
opened.

Hope this helps.
John
Project MVP