How to create a rolling date Lookahead Filter in Microsoft Project

The default lookahead filter in Microsoft Project, Date Range filter, only allows one to choose two hard dates for the start and end dates of the lookahead period. It sometimes becomes frustrating when you are always prompted to select the dates for a lookahead filter.

Won’t it be nice if we could set up a rolling date filter for our lookahead just like you would in Primavera P6, so that instead of hard dates, we can define our lookahead window as Status Date + 14 days or Current Date + 14 days for a 2-week lookahead?

At the end of this tutorial, you would learn how to set-up a rolling date filter in Microsoft Project using Flag custom field and how to save the filter to a Global Template so that it is available to you in other Microsoft Project files.

Ideally, you should set the Status Date of your schedule so that the lookahead filter uses it as reference point for determining the lookahead window but if you do not have the Status Date set, you can use Current Date anywhere I have Status Date in this tutorial.

The 2 criteria we need for a lookahead filter are:

1.     Tasks in progress: % Complete Greater Than 0% AND % Complete Less Than 100%.

a.     This criterion will capture any task that started before our lookahead window but not yet completed.

2.     Tasks starting within next [2 weeks]: Start Greater Than Status Date AND Start Less Than or Equal to Status Date + 14

a.     You can replace [2 weeks] with your lookahead window (e.g. 30-day, 90-day etc)

b.     14 represents 14 days in 2 weeks, therefore if your window is for 30 days, replace 14 with 30 or if it is a 90-day window, replace 14 with 90

c.      This criterion will capture all tasks with start dates within the lookahead window.

Applying these 2 criteria will enable us to capture in our lookahead filter, tasks that are already in progress or due to start or finish within 2 weeks of status date.

We do not need to set a finish criterion as any task due to finish within our lookahead window will be capture by criteria #1 if it is already in progress and if not , it will be captured by criterion #2 since for it to finish within next 2 weeks, it has to start within the same window.

To set Status Date, click on Project tab on the ribbon, and in the Properties group, click on Project Information and this will open the Project Information dialog.

In the Project Information dialog, set your Status Date and click OK to close the dialog.

I recommend creating a dedicated View for your lookahead filter and to do that let’s save a copy of an existing View. Save a copy of the View by clicking on Task tab on the ribbon, then Gantt Chart in the View group and Save View in the drop-down.

In the Save View dialog, enter “2-week Lookahead” in the Name box and click OK.

Next we insert a Flag custom field to our View, say Flag20 as shown in the diagram below.

We would use this Flag, which has a Yes or No option, to  determine tasks that are within the lookahead window using a formula to check for the 2 criteria we defined earlier. Where either criterion is true, our Flag will display Yes and where the criterion is false, our Flag will display No.

To set-up the formula, with your cursor on a cell in the Flag20 column, click on Gantt Chart Format tab in the ribbon and in the Columns group, click on Custom Fields to open the Custom Fields dialog.

Using the Rename button, change the name of Flag20 to 2wk LAH.

With 2wk LAH selected, click on the formula button.

In the Formula for dialog, enter the formula into the textbox and click OK.

([% Complete] > 0 AND [% Complete] < 100) OR ([Start] > [Status Date] AND [Start] <= [Status Date] + 14)

(Remember to use [Current Date] in place of [Status Date] if you do not have a Status Date set).

Click OK when prompted that existing data in the 2wk LAH field will be deleted.

And then click OK to close the Formula dialog.

Our View should now have some tasks with Yes flag in the 2wk LAH column and these are tasks that fulfil either of our two criteria.

Now, we need to define a filter based on the 2wk LAH field. In the View tab of the ribbon, click on the Filter dropdown within the Data group and from the dropdown, click on New Filter.

And this will open the Filter Definition in dialog, where we do the following.

• In Name textbox, enter 2-week Lookahead.

• Tick the Show in menu checkbox

• Under Field Name, select 2wk LAH.

• Under Test, select equals.

• Under Value(s), select Yes.

• Tick the Show related summary rows checkbox

• Then click Save to close dialog.

Our newly defined 2-week Lookahead filter should now be available in the Filter dropdown.

Click on it and our View should only display tasks with Yes in the 2wk LAH column.

You can hide the 2wk LAH column by dragging the vertical divider across the column.

And the good news is that our 2-week Lookahead Filter is automatically saved within our 2-week Lookahead View since it was created within the View. You can test this by changing Views and you will notice that our 2-week Lookahead View always has the 2-week Lookahead Filter applied.

So all you need to do every week, is change your Status Date, update your actuals, reforecast your schedule and the 2-week Lookahead View will automatically display only tasks within the lookahead window.

For steps on how to make the 2-week Lookahead filter available to all your  schedules, watch the embedded YouTube video and remember to subscribe to my channel.