Home › Forums › Discussion › Depicting column with finish date for specific activity in summarized projects › Reply To: Depicting column with finish date for specific activity in summarized projects
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.