Home › Forums › Microsoft Project Discussion Forum › Depicting column with finish date for specific activity in summarized projects
As an scheduler of a Program Management account I am dealing with a master schedule file consisting 80 or 90 subprojects from different teams.
To produce high level reports I normally depict a view with 1 single line per project with typical columns for name, duration, start finish… I want to keep 1 line per project as there too many projects but by doing this I lose start/finish date info from intermediate key milestones.
It will be extremely helpful to be able to show in a column the finish date of an specific intermediate milestone. Of course this milestone is already existing in all 80 or 90 subprojects and they are coded with the same value in a Text custom column.
I tried filtering only that milestone but start/finish columns will be still depicting the overall project info.
Is there any way?? I would appreciate any help so much!!
Thanks in advance for your time!!
Sergio, try this. Select one of your key milestones, select task information, and select the Rollup checkbox under the General tab. This rolls up the Gantt bar to the summary Gantt (and all summaries). You can do this to a task as well, but I would try a milestone first. There is also a way to rollup a task to the Gantt with color, but that’s a bit harder to do. With that you can show a specific color for each phase directly on the project task. If I remember correctly I did a webinar on how to do this that should be in the MPUG archives. Let me know your thoughts and I can try to dig it out.
I proceed as you told me and I can see know in the gantt diagram the milestone. I guess I could format it so it shows the date.
However, do you is it possible to show finish date of an specific milestone in a column by using a custom field? Maybe with a for5mula?
I am asking that because the info will be in the table format and it is more “easily exportable” to excel
I interpreted what you’re looking for slightly differently. As I think I understand it, you are trying to show one summary row for each of the sub-projects with all the normal project level summary row fields (start, finish, etc.) but in addition, you want to reflect one key milestone task in that same summary row. And short of showing multiple rows for each project, the summary row fields always summarizes all data for the sub-project. So here’s another approach that may work.
Add Column to view
In your sub-project, customize the Date1 and Date2 fields (whichever you want) to be the start/finish dates from this one milestone you want to add to the summary row. Add those date fields as columns to the desired view. Adjust the column heading to display a meaningful column title by right clicking on the column heading for Date1 for example, then clicking Field Settings in the pop-up menu, and then adding a custom column heading. For example, “Key Milestone Start”.
Now, I’m assuming you have some form of a custom value entered in the field Text1 on the key milestone. In my sample, I added the value “MS” to the custom test field Text1 to represent that.
Now we’ll set up a formula for each of the custom date field columns you added to the view (Date1 and Date2 in my example).
Right click on the Date1 field column heading and this time select Custom Fields. This takes us to the window where we can customize the Date1 field. Find and click on the Date1 field in the top list of Date fields. It should already be selected because we accessed it via the column heading. Under Custom Attributes, click the Formula radio button. Then click the Formula button itself.
You’ll now see an edit formula window. It will show Date1= followed by a formula entry window. Enter the following:
Yes, that’s “IIF”. The formula basically says this. If the value in field Text1 = “MS”, (its the special milestone) set the Date1 field to the task [Start] field value, otherwise set the value to “NA”. Text1 is the field with your special milestone designation code in it. If your code was ABC, then you’d replace the “MS” with “ABC” in the formula sample above.
Click OK to save the formula. What this does: If the task is one of your special milestones as designated by the “MS” value in Text1, it will copy the Start date field into the new custom Date1 field.
If you’re are displaying both Start/Finish dates on those special tasks, repeat the Setup Formula steps for the Date2 field, modifying the formula above to be
If this is truly a milestone, then the Start or Finish date should be all that’s needed, but in case you’re using a non-milestone field, you probably want to show both the start and finish values.
One additional note. In the formula, you must set the customized date field to “NA” if you don’t want a value in it. If you set it to something like Null or blank, the field displays “#ERROR” and the rollup value in the project summary will also show “#ERROR”. Setting it to NA will allow only actual values to roll up to the summary row.
Looking at the view
Once you have the view configured and the customized Date field added, the project summary row will show summarized values for the project level Start/Finish and other fields you’ve added, along with the Start/Finish dates for the task/milestone you designate with your special Text1 value. Except these start/finish dates will show under the newly added columns.
Setting up the Master Project is similar, but very abbreviated. In the Master project view, add the Date1 column. Right click on the Date1 column and select the Rollup Option again, assuming you want the Max or Min value from all the sub-projects to roll up into the Master Summary. If not, don’t worry about it.
Disclaimer: I haven’t played a lot with Master Projects. So you may need to tweak the column settings. Also, I did notice that the first time I opened the master with the subproject collapsed to one line, no values appeared in the customized Date1 field. Once I expanded the subproject to all the tasks, the Date1 field values summarized up to the Master summary row.
Hope that helps, or if nothing else, gives you some ideas.
Daryl adding the info in a column is right what I was looking for
I could not try it though yet as I am currently on a conf. call (I am so tempted to drop and start playing around with the schedule haha)
I will let you know tomorrow!
Thanks both for your time, really!
I did drop, could not wait to try it. And these are the results:
– I was quite disappointed it has to be developed both at a project level and at the masterschedule level. It will take some time to copy paste the fields with the organizer for all inflight projects but still worth it. And for new ones, this fields will be embedded in templates, so nothing to worry about
– You were right, once in the masterschedule you need to expand the summary project line for the values to appear; it is like a refresh thing. Again it worth it to expand all and then outline back at correspondent level once every morning to get those values
As a conclusion: it does work and is a very useful tool when working with several projects
A pleasure gents!
Glad it works! One quick thought. You might be able to quickly expand and collapse all the schedules in a couple mouse clicks. Click the Task Name column heading to select the entire column. In the View ribbon, click the drop down arrow on the Outline icon in the Data group. From that popup menu, select Hide Subtasks, and then from the menu a second time select Level 1. That should expand every schedule and then collapse down to the subproject level in two clicks!