Stoplight formula

[Dan]

About a month ago, Dale Howard, MVP pointed me toward an excellent site on
creating stoplight indicators, which we subsequently did. All is moving
forward smoothly, so far, thanks to him.
I have set up the indicators as per the paper and they all appear in good
style.
My issue is that we would like to do the following:
• If today’s date is greater than 20d to the baseline, we want a red light,
and so on, as the paper indicates; however, we would want the lights to
change on the delinquent items upon opening the file.
Right now, when we call up the project from the server, all lights stay
green, until I manually enter today’s date in the finish column on those
actions that are delinquent. They then change to the appropriate color, per
your paper.
Is there a formula I could enter that would use the comparison of today’s
date to the baseline finish date that would automatically change the lights
on actions delinquent to today’s date?
I hope I’ve clearly stated the issue. Thanks for any help anyone would care
to provide.

 

[Jim Aksel]

Dan - Try using the Progress Date in a formula. That way you could create a
value that is something like IF(Progress Date > Baseline Finish + 20, True,
False) Then you can key your stop light to the true/false value.

To think about. WHy not use the Schedule Performance Index (SPI) t drive
you traffic light? Regardless of days late, your could still be in trouble.
Suppose you had a task that was 40 days in duration. You need to be showing
progress in accordance with your baseline. If not, you could still be in
real trouble without being past the finish date. So, the SPI will uncover
this for you.

 

[Dan]

Jim:
Thanks for the tips. I am not familiar with either Progress Date or SPI, but
will look into them. You must remember that I'm dumber than I look<g>, so
progress may be slow, but I will check them out.

Any other ideas would be appreciated!

Thanks,
Dan

 

[Jim Aksel]

You can research "Earned Value Management System" or EVMS in the Project
Help. or on Google. It's a great education. Other terms would be Cost
Performance Index (CPI)

 

[Dan]

Will do and thank you very much!!
Dan

You can research "Earned Value Management System" or EVMS in the Project
Help. or on Google. It's a great education. Other terms would be Cost
Performance Index (CPI)

 

[AM]

Dan,

I am doing something similar could you please point me to the
paper/site you mention here.We are trying to track progress against the
% complete and we decided to have a Forecast % complete each week.
Based on the deltas we would change the color of the status. But I
realized there is no such field(Forecast % complete) and so need to
figure out how we should do this. Please advice.

Thanks
AM

 

[Dan]

AM:
Here's the site I was directed to by Dale: Refer to the following FAQ for an
example of how to do it:

http://www.projectserverexperts.com/Shared Documents/CreateStoplightIndicators.htm

If you are not using Project Server with Microsoft Project, then simply
ignore the Project Server content. Hope this helps.

This is a lengthy process, but it does work. If you can figure out the
formula that would change with the % complete, I could use that one!

Hope this helps

 

[Dan]

Jim:

The SPI approach won't work for me, as we don't track the time worked.
However, I do believe your other idea of using the Progress Date will work.
Search as I might, I've been unable to find any reference to a "Progress
Date" entry in Project Help and I do not find it listed in any of the fields
available in the formula section.

Would you be kind enough to point me more specifically?
Dan

 

[AM]

Thanks Dan, I am sure this should be great help. I am plannning to use
the physical % complete as my forecast % complete since it does not
impact anything. So I am going to maually fill that column each week as
to what was forecasted and then find deltas between actual and
frecasted. I am going to have one more column called Status which will
actually change with the deltas...so if >10% then it would be "red"
something like that.

So in that column I would use the formula [% complete]-[Physical %
complete] and set the conditions for graphical indicators.

It is not the most conventional way but hopefully this should work!I

-AM

 

[Dan]

OK, Thanks! I'm not checked out on the subtleties between Physical % complete
and Forecast % complete. Can you explain the differences?
Dan

Thanks Dan, I am sure this should be great help. I am plannning to use
the physical % complete as my forecast % complete since it does not
impact anything. So I am going to maually fill that column each week as
to what was forecasted and then find deltas between actual and
frecasted. I am going to have one more column called Status which will
actually change with the deltas...so if >10% then it would be "red"
something like that.

So in that column I would use the formula [% complete]-[Physical %
complete] and set the conditions for graphical indicators.

It is not the most conventional way but hopefully this should work!I

-AM

AM:
Here's the site I was directed to by Dale: Refer to the following FAQ for an
example of how to do it:

http://www.projectserverexperts.com/Shared Documents/CreateStoplightIndicators.htm

If you are not using Project Server with Microsoft Project, then simply
ignore the Project Server content. Hope this helps.

This is a lengthy process, but it does work. If you can figure out the
formula that would change with the % complete, I could use that one!

Hope this helps