none
Rag status för milestones RRS feed

  • 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

Answers

  • 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


    Paul Mather | Twitter | http://pwmather.wordpress.com | CPS | MVP | Downloads

    Wednesday, March 2, 2016 1:43 PM
    Moderator
  • 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
    Moderator

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


    Paul Mather | Twitter | http://pwmather.wordpress.com | CPS | MVP | Downloads

    Wednesday, March 2, 2016 1:43 PM
    Moderator
  • 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
    Moderator
  • Hi Ben,

    Thanks for your promt reply! It worked!!!

    Many thanks!

    Wednesday, March 2, 2016 2:18 PM
  • Glad it did.  Paul's would work too.

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

    Wednesday, March 2, 2016 2:28 PM
    Moderator