none
Creation of traffic Lights for Baseline Date and effort Variance RRS feed

  • Question

  •  All,

    I am new to this forum, and only just starting to MS project in a more advanced setting. I have been working a project plan with multiple project stages, and to make the project plan more 'user friendly' I was thinking of having a column titled 'Baseline Variance traffic light'  in my project plan, based on the below listed rules:

    • If the Finish Date is <=Today's Date and % complete equals 100%, then display the green graphical indicator
    • If the Finish Date is >Today's Date and the % complete is <100%, then display the green graphical indicator
    • If the Finish Date equals Today's Date and the % complete is <100%, then display the yellow graphical indicator
    • If the Finish Date is <=1 week from Today's date and % complete <100%, then display the yellow graphical indicator
    • If the Finish Date is >1 week from Today's Date and % complete <100%, then display the red graphical indicator
    • If the Finish Date is <=Today's Date and % complete equals 100%, then display the green graphical indicator
    • If the Finish Date is >Today's Date and the % complete is <100%, then display the green graphical indicator
    • If the Finish Date equals Today's Date and the % complete is <100%, then display the yellow graphical indicator
    • If the Finish Date is <=1 week from Today's date and % complete <100%, then display the yellow graphical indicator
    • If the Finish Date is >1 week from Today's Date and % complete <100%, then display the red graphical indicator

    The question that I have is how do I create the traffic light system to make my project plan more user friendly for reporting purposes.

    Any help/input will be greatly received.

    Thomas B. Simonsen

    Sunday, January 3, 2016 2:23 PM

All replies

  • Thomas,

    Before you venture off and create a bunch of custom fields, have you looked at the built-in Status field? It might just give you enough of what you need.

    Beyond that I have a few comments on your "rules". First, your list is half duplicates, so dump those out. Beyond that....


    1. If the Finish Date is <=Today's Date and % complete equals 100%, then display the green graphical indicator
    All this really needs to say is: " if % complete = 100% then green". Done is done, it doesn't matter when.

    2. If the Finish Date is >Today's Date and the % complete is <100%, then display the green graphical indicator
    Conflicts with criteria in item 5

    3. If the Finish Date equals Today's Date and the % complete is <100%, then display the yellow graphical indicator

    This criteria will be covered by criteria 4, so 3 is redundant

    4. If the Finish Date is <=1 week from Today's date and % complete <100%, then display the yellow graphical indicator

    5. If the Finish Date is >1 week from Today's Date and % complete <100%, then display the red graphical indicator

    This should probably be a green indicator (i.e. finish far in the future)

    John


    • Edited by John - Project Sunday, January 3, 2016 5:27 PM ligic update
    Sunday, January 3, 2016 4:36 PM
  • You can use the Switch function to drive a custom field and then attach graphical indicators to the field. so:

    Switch([Finish] <= Now() And [% Complete] = 100, 1, [Finish] > Now() And [% Complete] < 100, 1, [Finish] = Now() and [% Complete] < 100, 2, etc.)

    The set the graphical indicators so that 1=Green Stoplight, 2= Yellow, etc.

    You may want to use the project's Current Date or Status Date field instead of the Now() function, depending on what you mean by "Today's Date."

    Monday, January 4, 2016 2:42 PM
  • Your formulae do not reference baseline dates at all. So your tasks can have green lights even if they are weeks behind schedule and red even if it is weeks ahead of schedule.

    As John said you need to reduce the number of rules and I would check exactly what is needed to be reported. Giving a wrong impression can get expensive!


    Rod Gill
    Author of the one and only Project VBA Book
    www.project-systems.co.nz

    Tuesday, January 5, 2016 7:48 PM
    Moderator
  • Dear Bill

    Many thanks for your reply, and with your input I have managed to create the traffic lights for my project plan.

    Best regards

    Thomas 

    Thursday, January 7, 2016 1:56 PM
  • Dear John

    Many thanks for your input, and on basis of your and another contributor I have managed to achieve the traffic light for my project plan.

    Thomas

    Thursday, January 7, 2016 2:27 PM
  • Thomas,

    You're welcome and thanks for the feedback.

    John

    Thursday, January 7, 2016 5:14 PM