Using Formulas to Filter Dates in a Master Project File

Recently we had a user ask us how they might automatically populate their flag columns to allow a configurable start and end date constraint which would capture any task that starts, finishes, or is in the middle of execution.  Essentially, the business requirements for the report looked like this:

Date Boundaries

The problem for us was the formula, as written, would not work due to the fact that we were working within a master project file with many subprojects.  Here was our simple formula, which worked in the individual subprojects, but not if you looked at them all within the master file:

IIf([Finish]<CDate(“1/1/2014”) Or [Start]>CDate(“2/28/2014″),”No”,”Yes”)

RIDDLE SOLVED!

While we like to pride ourselves on our knowledge of Microsoft Project, we don’t always have the answers.  In this case, some helpful folks in a couple of user groups on LinkedIn (links at the bottom of this post) politely pointed out that if you want a formula to work in a Master you must copy that formula to each subproject and the Master, which can most efficiently be accomplished using the Organizer.  To reach the Organizer, go to File -> Info and click the Organizer button.

Organizer

Once inside, simply ensure you’re working with the right source file where you formula exists, find the custom flag column that you’re trying to copy, and then copy it to each subproject and the master (or from the master to the subprojects if you build your formula there) using the dropdowns and you’re done.

Organizer Actual

At this time, your flag values will be set correctly, and you will be able to grab the correct tasks for your OnePager Pro report.  Whenever you need to modify the formula, simply change the formula in your master, and all subprojects should pick up the change.

Flag Column Change in Master

You can then further constrain the start and end boundaries of your visual within OnePager, as needed, within the Main tab of the Project-View Properties settings prior to presentation.

Using Formula in Flag Column Within MasterMicrosoft Project Professionals Network

Microsoft Project Users Group

MPUG Global

This entry was posted in Microsoft Project Formulas, Microsoft Project Tips by Jay. Bookmark the permalink.

About Jay

Jay carries with him fourteen years of project management experience within the cable, telecom, construction, software development, and energy industries. The spectrum of projects and programs that Jay has managed throughout his career is broad and deep, enabling him to help clients implement OnePager software in a multitude of applications.

Leave a Reply

Your email address will not be published. Required fields are marked *