none
Pulling a task start date in a Master Schedule column RRS feed

  • Question

  • I'd like to know if its possible to show the start date of a particular task in a column of a simple master schedule.

    I have a master schedule of several similar projects. The projects are collapsed to a single line for presentation purposes, to give an simple overview of all the projects we're working on.

    Each project has the tasks: Design, Construction, Occupancy and Closeout. The problem is, the default Start and Finish of the project is deceiving because we'd really like to see the start of occupancy as the so called "finish date" rather than the end of Closeout, which is sometimes months long.  

    Is there a way to have a column that just pulls the Occupancy start dates? 

    Tuesday, June 4, 2013 2:15 PM

All replies

  • Have you considered using the timeline view and adding those key milestones to the timeline?  You can certainly add a spare date field and manually enter the Occupancy start dates - you'll just need to manually update the date if the milestone slips. 
    Tuesday, June 4, 2013 2:28 PM
    Moderator
  • I think manually entering the date might be the direction we have to go.  Unfortunately we wont be able to use the timeline. Ideally though, I wanted to avoid the manual labor and potential for errors that might happen with individually entering the data, especially since the data is already there, it just needs to be pulled. 
    Tuesday, June 4, 2013 2:34 PM
  • After a bit of experimenting, there may be a less manual method.  It requires two custom fields, one Flag field and one date field.

    1. Insert one of the custom flag fields.  Set the Flag field to "yes" for the Occupancy task. (in the formula below I used the Flag1 field.
    2. Insert a spare Finish field (I used Finish1).
    3. In the Finish1 field enter the following formula:

            IIF([Flag1]=-1, [Finish], "NA")

      4. The Finish1 field should now have the finish of the Flagged task.

      5. When you insert the project into the master, display the Finish1 field, select Custom Fields from the shortcut menu and rollup the field in the "Calculation for task and group summary rows."

    You'd need a separate field for every inserted project, but that takes the 'manual' portion out.

    Tuesday, June 4, 2013 2:48 PM
    Moderator
  • jsf9356,

    Actually I thought Julie's suggestion of using the timeline was the best solution but it that just won't work for you, you can get the desired date into an extra date field of the summary line insertion point of each subproject with a little VBA. All automatic, no manual entry and it would be displayed with the master file collapsed. All that is needed is some way to identify the particular task in each subproject.

    Or, here's another approach that could also work. Set a flag field for the particular task in each subproject that has the desired occupancy date and the summary line insertion point for each subproject. Expand the master file and then filter on the flag. Do not set the option to show related summary lines in the filter since that will give you more than what you want.

    John

    Tuesday, June 4, 2013 2:54 PM
  • Julie,

    I've tried this a few times, but it doesn't seem to work.

    • My formula for my Start1 is:  IIf([Flag5]=-1,[Finish],"Nope")
    • Custom Attributes: Formula (see above)
    • Calculation for task and group summary: Rollup - Maximum
    • Calculation for assignment rows: None
    • Values to display: Data

    I will either get a #Error or NA returned.  Any ideas?

    Thanks!

    Thursday, June 6, 2013 4:20 PM
  • Hi,

    Did you set Flag5 to yes for the tasks you wanted the data from?  You also cannot use "Nope" in a date field.  Please try changing it back to "NA".

    Thursday, June 6, 2013 4:46 PM
    Moderator
  • Julie,

    Yes, Flag5 is set for the Occupancy summaries.  It serves as a Flag for a bar style as well.  Also, I set the false return as "Nope" to hopefully distinguish it from a null result.  I'll look into it and try it again.

    Thursday, June 6, 2013 5:14 PM
  • Let us know how you get along.  Date fields do not have a "null" result - the "NA" result is not null.
    Sunday, June 9, 2013 1:06 PM
    Moderator
  • Julie,

    Thanks for the follow up.  If NA is not null, what does it mean? 

    I've tried this method several times and in several ways, but the way we're doing it now is to simply insert a custom date column, then "paste-special" the Occupancy start of each project.  This makes it a dynamic link that will adjust should the project slip.  It takes manually doing it for each inserted project, but if it's only done once I think we can manage.

    Thanks for the help. I'll keep trying the formulas and report back if I can get a hit.

    Monday, June 10, 2013 12:49 PM
  • "NA" in a date field is actually a very large number in Project.

    See:

    http://zo-d.com/blog/archives/programming/working-with-custom-field-formulas.html

    for a discussion on using the ProjDateValue("NA") in formulas. (It doesn't apply to what you need).

    I think the issue you may also have with the formula is it sounds like you have tagged the summary task with Flag5 - try tagging the subtask and see if that rolls the value up correctly.

    The paste/special should work - but be careful - it relies on old DDE technology and may get a bit flaky.

    Monday, June 10, 2013 1:29 PM
    Moderator
  • jsf9356,

    Pardon my butting in but I feel I need to enhance the caution Julie gave about using paste links. If you are using paste links you are flirting with disaster. You already have a master/subproject structure. That is tentative enough. Adding paste links into the mix is basically asking for file corruption. There are better, safer ways to get where you want to go.

    Get rid of those paste links and go with Julie's approach. If for some reason that just doesn't work, I'll give you a VBA approach.

    John

    Monday, June 10, 2013 2:16 PM
  • Julie,

    I've decided to approach this again, but am still having issues with the date values showing up.  I've attached a screenshot of what I have so far. I've tagged MS: Occupancy Complete as my Flag 7 Go Live. Projected Go Live column is a custom Start with information shown. The only lead I got so far is that when I switch the "Calculation for task and group summary rows" to Maximum or Minimum, the highlighted cell, and the MS Occupancy date cell flashes #Error, then goes back to NA. It's almost like it's starting to do what I want it to, but then stops.

    Thanks for any help you can provide!

    Wednesday, July 17, 2013 6:50 PM
  • jsf9356,

    In a formula flag fields are either true or false, not 1 or 0.

    Hope this helps.

    John

    Wednesday, July 17, 2013 9:28 PM
  • But they can be negative 1 or zero.  I think his formula shows negative one (-1).  It looks like you have rolled up the flag fields to the "Project" task.

    Do you have SP-1 installed to Project 2010?  I'm guessing you are writing the formula in the master project?

    Wednesday, July 17, 2013 10:00 PM
    Moderator
  • I've just tested again.  I have the formula written in the subproject file.  I insert the subproject into a master, add Flag7 and Start1, set the rollup to "Maximum" and all works just fine.
    Wednesday, July 17, 2013 10:02 PM
    Moderator
  • Julie,

    You're right. I missed that one.

    John

    Wednesday, July 17, 2013 11:04 PM