Stop Light Formula

[dstoddart@blueyonder]

Hello

I am trying to set-up a Stop Light function for the tasks in my project plan so I can have a Red, Amber, Green status for each task. I want to use the Finish Variance field to show the status of a task so if it's >5 but less than 10 to show Amber, for a variance of >10 it show red otherwise it should show green. I have spent hours this afternoon trying to create a formula within the custom field function to do this without any luck. If anyone can help with a suitable formula or advise on how I can achieve the same thing, I would be very grateful and it would save my frustrations tomorrow when I have another go !

Many thanks
Dave

[corky0770]

You can try the formula in the number field. It will be helpful for you to deal with the issue and I think it is going to work really well. The formula that I am posting below will help you to some extent:
Switch([Finish Variance]/[Minutes Per Day]<=5,1,[FinishVariance]/[Minutes Per Day]>5 And [Finish Variance]/[Minutes Per Day]<=10,2,[Finish Variance]/[Minutes Per Day]>10,3)

[dstoddart@blueyonder]

The only possible way it could be improved is if it could ignore tasks that are already complete. I tried to amend the formula you kindly provided using "AND [%Complete],100" but this didn't do the trick. Just struggling a little to get my head around these formulas. Any ideas ?

you're a star and have saved me so much trouble. I don't confess to understand the formula but it does the trick. Are there any resources I can get my hands on that tells me more about these formulas and what they do ? I have a couple of Project 2007 books but they don't do the subject justice. If you know of any books or internet sources that cover this topic I'd be grateful to know.
Thanks again

[Illeana]

It looks the formula works for you. The new thing that you ask, you will have to use [% Complete] = 100, 0 at the start of formula. That will might help you more. The actual formation after adding what I have told above is : Switch([% Complete]=100,0,[Finish Variance]/[Minutes Per Day]<=5,1,[Finish Variance]/[Minutes Per Day]>5 And [Finish
Variance]/[Minutes Per Day]<=10,2,[Finish Variance]/[Minutes Per Day]>10,3)

[texmaster]

I've tried pasting this formula into a number field as described but I continue to get a syntax error and it highlights the second [Finish Variance]

Switch([Finish Variance]/[Minutes Per Day]<=5,1,[Finish
Variance]/[Minutes Per Day]>5 And [Finish Variance]/[Minutes Per
Day]<=10,2,[Finish Variance]/[Minutes Per Day]>10,3)

[texmaster]

Perhaps I'm not approaching this the right way.

I'm looking for an easy way with the streetlight format of checking the progress of a project by green yellow and red based on say the Finish column for example.

ie if a task is more than 5 days before completion without being 100% complete its green, less than 5 yellow and past the finish date red.

[ttate]

I'm attempting to set a stop light indicator that will indicate the status of the task based on the start date. For example all tasks that are >10 days prior to the start date would be green, when the start date approaches and is <10days the indicator would eluminate yellow, once the start date is reached and is =to or >than, the indicator would turn red. I've worked for several hours attempting to write the formula, with no luck. I too would like to make sure also that once all tasks have completed at 100% they show green.

Thank you in advance for your assistance. BTW, I'm using MSProject 2007.

[MrG258]

I too have recently been looking at this. I found on another site the following formula:

IIf(([% Complete]<100 And [Finish]<[Current Date]) Or ([Start]<[Current Date] And [% Complete]=0) Or ([% Complete]<100 And [Finish]<=[Current Date]),"Yes","No")

but this only gives Yes or No as a result. I am after three results....

Can this formula be changed to achieve the following requirements:

1. Red if more than 7days overdue (start or finish)
2. Yellow for up to 7 days overdue (start of finish)
3. Green for on time (either completed within time or yet to commence)

I too am using 2007.

Thanks in advance

[JulieS]

ttate & MrG258,

One of the first things you'll both need to sort out is you are trying to compare data (Start or Finish dates) to something -- but you haven't specified what. Most commonly, we compare to baseline data -- either baseline start or baseline finish. There is already calculated fields for Start Variance and Finish Variance which calculate the number of days difference between either the Baseline Start and Start or the Baseline Finish and Finish.

To specifics -- ttate, you are asking (I think) to compare a start date of a task to some other start date -- and I don't understand what you mean. "For example, all tasks that are > 10 day to the start date...." what start date? Are you trying to see if they are more than 10 days before current date? That would not really show you status as planned -- just upcoming tasks.

The following formula (in a text field ) should get you started:
Switch([% Complete]=100,"Complete",DateDiff("d",[Current Date],[Start])>10,"Future Task",DateDiff("d",[Current Date],[Start])<=0,"Late",DateDiff("d",[Current Date],[Start])<=10,"Current Task")

It uses the DateDiff function to calculate the difference between the current date and the start date of a task. If the task is 100% complete the text field shows Complete. If the start date is greater than 10 days from today, "Future Task". If the task is today or in the past, "Late" and if the task is in the future but less than 10 days away "Current Task". The flaw that I see with the logic is that it pays no attention to whether the task is in progress or not.

MrG258 - Again, I can only guess that you are trying to look at Start or Finish Variance as calculated by the baseline?

If so, try the following for start (also in a text field):

Switch( [Finish Variance]/[Minutes Per Day]<=7,"Yellow",[Finish Variance]/[Minutes Per Day]>7,"Red", [Actual Start]=ProjDateValue("NA") And [Current Date]<[Start],"Green",)

Again, the logic flaw here is that even if a task is scheduled for the future, it may already be 'red' if it's finish variance is greater than 7 days.

I have not tested either of these formulas extensively, but try using them as a starting point and post back with further questions as needed.

Julie

[Adam 1980]

Hello,

I'm currently using a formula allowing me to show trafiic lights (RAG). My formual also works well for the subtasks, but the highest level summary tasks shows the RAG status for the overall duration date. What I need is the RAG Status on the summery task to reflect the the subtask with the shortest time left,the subtask that will expire first. e.g. if there are 6 subtasks under the summery level task, from which 5 will expire after 2 months and are on GREEN Status and 1 will expire within next 5 days and is AMBER, because it's approuching deadline, I want the high level summery task to be AMBER and reflect the status of the task which is on AMBER (the one expiring in 5 days). The formula im using is pasted below.

IIf([% Complete]<100,IIf([Finish]>Now()+7, "OK", IIf(Now()>[Finish],"Over","Nearly Expired")),"Complete")

Could you help please? It will be very much appreciated.

Many Thanks

Adam

[JulieS]

Hi Adam,

I'm sorry, but I don't believe you can accomplish this with custom formulas. As you know, you can create the calculation at the subtask or use the same forumula at the summary row -- however other than the rollup functions, you cannot have a different formula for the summary.

You may be able to accomplish this through a VBA formula to calculate the minimum date. I'm afraid I can't be of much help there -- however try posting your question to the customization forum at Technet:

http://social.technet.microsoft.com/...stprog/threads

When you post, please state explicitly what version of Project you are using as that does make a difference.

Good luck.
Julie