Such Happiness In Thought Happens

October 3, 2008

Milestones past their Due Date

Filed under: MS Office — Tags: , — Duane @ 7:37 pm

This post is in response to a comment made by a reader recently in the Project Tips & Tricks post earlier this year. The comment from Natasha is as follows:
“Is there anyway to make past due milestones a certain color?? I have green for milestones that were complete, black for milestones that are in the future, and I’d like to make milestones that the date has already passed but the % complete is zero = Red. how can i do this?”

Well Natasha, this post answers that question. So read on and enjoy!

I had a bit of a think about this after reading the comment and in the end decided using a Flag field would be the best way to do this. So the first thing to do is to Customise a Flag field with a formula where we compare the Start Date of any task/milestone with today. The reason I went with the current date is to represent where the “date has passed”. You could use Start Date and Baseline Date, but until the milestone is done, there wouldn’t be any value in Start Date, thus unworkable. Anyway …

  • Go to Tools>Customize>Fields.
  • Choose the Flag field type from the dropdown box. It is your choice if you rename the Flag1 field, I didn’t bother.
  • Press the Formula button and enter the following formula:
    IIf([Start]<Now(),”Yes”,”No”)

  • Press OK.

Then go to your Gantt Chart Bar Styles. I commonly double click any available space of the Gantt Chart.

  • Create a new Bar Style by going to the next blank line.
  • Name it Past Milestone (or something else that suits you better)
  • In the “Show For … Tasks”, choose Milestone, type comma (,), then choose Flag1
  • Change the “To” field to Start.
  • Change the Appearance to a Solid Diamond with a colour of your choice.

  • Press OK.

The following screen shots show what happens with a milestone that is before and after the date of 3rd October 2008 (the Now() date when I created this post). The No and Yes values that are visible are from the Flag1 field that I inserted into the Task Sheet.

The next set of screen shots are what happens when the task is 100% complete with this new Milestone Bar Style pre & post today. As can be seen, it does not matter when the Milestone is completed, it still turns green (the Completed Milestone colour)

Hopefully that answers your question Natasha. I know it doesn’t fully look at the question you asked in regard to a milestone past its Due Date. I had thought of Actual Start greater than Baseline Start, but this means you have to complete the milestone; there is the option of Not Started that I noticed in the Bar Style “Show for … Tasks” area, but this also doesn’t fully answer your question, unless you made the Flag1 field use a formula of IIf({% Complete]=0,”Yes”,”No”) and made the “Show for … Tasks” Milestone,Not Started,Flag1 and see if that works.

Advertisements

2 Comments »

  1. Thanks so much for the feedback!! It didn’t actually do what I was looking for from the initial instructions. But I tried changing the flag formula to the % Complete as you suggested, and then changing the “show for…tasks” field to include “not started” and this worked. Now all my overdue milestones are red!! Thanks!

    Comment by Natasha — October 4, 2008 @ 3:47 am

  2. I’m still having complications with this. Somehow things got changed, i think it’s because i’m fooling around with too many flag formulas. I want the following scenario:
    -milestones that have been completed (100%) = green
    -milestones that are past due and less than 100% = red
    -milestones that are in the future, after the current day = black (regardless on the percentage complete (unless they are 100% in which they can be green))

    Also, I have a MP schedule for a program, which has many underlying projects underneath. I want the milestone marker to show on each line: the task summary line, the project summary line, AND the “program” summary line (the very first line in my MP schedule that has all the underlying projects underneath it).

    Any help would be great! I really messed these up! I’m not good with formulas.

    Comment by Natasha — October 7, 2008 @ 4:43 am


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: