none
Customized Traffic Light Status Indicator RRS feed

  • Question

  • Hello-

    I am trying to program a custom status indicator.  I would like to use the following guidelines for the indicator:

    Indicator Criteria Stop Light Color Words
    If task %Complete is 0 and start date was before today Red Late Start
    If task %Complete is not 100% and finish date was before today Red Late Finish
    If % complete >= the prorated expected completion Green On-Track
    If it’s not scheduled to start yet Green On-Track
    If % complete is greater than 10% less than the prorated expected completion Yellow At-Risk

    I have tried the following formula but I'm getting errors and I know I'm not programming the last part correctly (in Bold).  If someone could help me out that would be much appreciated!

    IIf([% Complete] = 0 And [Start]<Now(), "Late Start", IIf(([% Complete] <>100 And [Finish]<Now()), "Late Finish", IIf([% Complete]>=100*(Abs(ProjDateDiff([Scheduled Start],Now())/(ProjDateDiff([Scheduled Start],[Scheduled Finish]))), "OnTrack", IIf([Start]>= Now(), "OnTrack".  IIf([% Complete]>=100*(Abs(ProjDateDiff([Scheduled Start],Now())/(ProjDateDiff([Scheduled Start],[Scheduled Finish])))), "At-Risk"

    Thursday, March 17, 2016 11:49 PM

All replies

  • Spirito96,

    Could it be as simple as the errant period (".") instead of a comma (",") after the second "on Track"?

    Also, I count 21 open parenthesis but only 15 close parenthesis.

    Is there a reason you're not simply using the Duration field instead of using the ProjDateDiff between Scheduled Start and Scheduled Finish? It could cut down on some bulk and complexity. Just be aware that Project stores all time based data in minutes so some conversion may be necessary.

    Complex formulas like this are very hard to troubleshoot. I normally break them down into separate individual formulas in multiple fields and get them working at that level. Then I combine each separate part into larger pieces and get that working. The end result is to work up to the complete formula.

    Hope this helps.

    John


    Friday, March 18, 2016 12:03 AM
  • Please don't forget to set 'Values to display' along with the formula. Refer below link

    https://msdn.microsoft.com/en-us/library/office/ee767700(v=office.14).aspx


    Regards, Syed Faizan ur Rehman, CBPM®,PRINCE2®, MCTS

    Wednesday, April 6, 2016 7:34 AM