Rag status för milestones

• Question

• Hi,

I am using Switch formula to set rag status for the milestones in the project. I cannot get it to work properly and I am very greatful for your help!

What I want to have is

If the milestone % = complete display"Complete"

If the milestone is new and Baseline start and finish shows "NA" display "No BL"

If the milestone has a finish variance <= 0 display "Green"

If the milestone is between 1 and 5 days delayed display "Yellow"

If the milestone i dalyed by over 5 days display "Red"

The formula I have written but not working is:

Switch([% Complete]=100;"Complete";(([Baseline Start]=ProjDateValue("NA")) Or ([Baseline Finish]=ProjDateValue("NA")));"No BL";[Finish Variance]<=0;"Green";[Finish Variance]<=5;"Yellow";[Finish Variance]>5;"Red")

The version is Microsoft Project 2010. Very greatful for your help!

Thanks

Wednesday, March 2, 2016 1:20 PM

• Hello,

Does this work for you:

Switch([% Complete]=100,"Complete",(([Baseline Start]=ProjDateValue("NA")) Or ([Baseline Finish]=ProjDateValue("NA"))),"No BL",[Finish Variance]/480>5,"Red",[Finish Variance]/480>0,"Yellow",[Finish Variance]<=0,"Green")

You migth need swap out , for ; as you locale separator might be different.

Paul

Wednesday, March 2, 2016 1:43 PM
• Hi FredScando,

The issue is the number 5 (assuming that the delimiters for your country setting are ";" - in the UK they are "," so I had to change that for the formula to work.  Everything in MSP is held in minutes, so 5 actually is 5 mins, and not 5 days.  There are 480 mins in a 8 hour day, or you can use the [minutes per day] field.  The correct formula for me is...

`Switch([% Complete]=100,"Complete",(([Baseline Start]=ProjDateValue("NA")) Or ([Baseline Finish]=ProjDateValue("NA"))),"No BL",[Finish Variance]<=0,"Green",[Finish Variance]<=5*[Minutes Per Day],"Yellow",[Finish Variance]>5*[Minutes Per Day],"Red")`

Ben Howard [MVP] | web | blog | book | P2O

Wednesday, March 2, 2016 1:44 PM

All replies

• Hello,

Does this work for you:

Switch([% Complete]=100,"Complete",(([Baseline Start]=ProjDateValue("NA")) Or ([Baseline Finish]=ProjDateValue("NA"))),"No BL",[Finish Variance]/480>5,"Red",[Finish Variance]/480>0,"Yellow",[Finish Variance]<=0,"Green")

You migth need swap out , for ; as you locale separator might be different.

Paul

Wednesday, March 2, 2016 1:43 PM
• Hi FredScando,

The issue is the number 5 (assuming that the delimiters for your country setting are ";" - in the UK they are "," so I had to change that for the formula to work.  Everything in MSP is held in minutes, so 5 actually is 5 mins, and not 5 days.  There are 480 mins in a 8 hour day, or you can use the [minutes per day] field.  The correct formula for me is...

`Switch([% Complete]=100,"Complete",(([Baseline Start]=ProjDateValue("NA")) Or ([Baseline Finish]=ProjDateValue("NA"))),"No BL",[Finish Variance]<=0,"Green",[Finish Variance]<=5*[Minutes Per Day],"Yellow",[Finish Variance]>5*[Minutes Per Day],"Red")`

Ben Howard [MVP] | web | blog | book | P2O

Wednesday, March 2, 2016 1:44 PM
• Hi Ben,