In this tutorial I will explain on how to create the “Graphical Indicators” which can be used to represent status of the project activities visually. Tutorial specificaly details on how to create graphical status indicators in Gantt Chart Microsoft Project. Graphical indicators are visual representation of the status of the tasks based on the percentage completion. Visual representation gives a quick overview of all tasks status. This is a quick and easy way to read through your project gantt chart to identify or segregate tasks that are completed or running late.
Scenario 01: Assign Green indicator for project activities that are 100% complete.
Scenario 02: Assign Red indicator for project activities which passed the Finish date and are not 100% complete.
Scenario 03: Assign Red Flags for activities that are 0% complete and has passed the planned start date.
Scenario 04: Assign Yellow indicator for activities that are upcoming in 2 weeks. ( you can alter this to 1 week or 2 days etc based on your requirement)
Scenario 05: Assign White indicator for project activities that are yet to start.
Scenario 06: Assign Dark Green indicators for all other project activities which doesn’t fall in to any of the above scenarios.
Now lets see the formula to see how we can achieve the above scenarios using IF expression in MS Project.
Formula
IIf([% Complete]=100,4,(IIf([Current Date]>=[Finish],3,(IIf([Current Date]>[Start] And [% Complete]=0,5,(IIf([Current Date]>[Finish]-14 And [% Complete]<80,2,(IIf([Current Date]<[Start],6,1)))))))))
As you know the MS Project can return numerics for the true & false part of the expression. So the numbers used in the above formulas showed be now mapped to respective colors in the graphical indicator button in the formula window.
Checkout my Youtube channel to see the video tutorial. Please like the video and subscribe for more content.
Hi
It is good tutorial. However, I observed one error in this. In ms project 2016 you can inactive tasks. But in above formula it takes inactive tasks also into consideration. You may need to fix it.
Thanks
Priyank
Hi
Below is the scenario and corrected formula. I suggest to have result value in sequence-as i did here.
Scenario 01: Assign Green indicator for project activities that are 100% complete. ( 1 Green)
Scenario 02: Assign Red indicator for project activities which passed the Finish date and are not 100% complete.(2 Red Indicator)
Scenario 03: Assign Red Flags for activities that are 0% complete and has passed the planned start date. (3 Red Flag)
Formula
IIf([Active]=Yes,IIf([% Complete]=100,1,(IIf([Status Date]>=[Baseline Finish],2,(IIf([Status Date]>[Baseline Start] And [% Complete]=0,3,(IIf([Status Date]>[Baseline Finish]-14 And [% Complete]<80,4,(IIf([Status Date]<[Baseline Start],5,6))))))))))
Thanks
Priyank
Thanks Priyank Sharma
Wanting to have blue for complete, green for on track, yellow for due in two weeks, red for past due and white for no yet started. How does the formula need to change?
Hi Linda, if you watch the YouTube video that I have posted on that page, you can scroll to 6:49 and you will see that I am picking a color from the drop down, you can pick any color that satisfies your requirements. Here is the direct link to video https://youtu.be/Pl8ZW5GbB6Q
Hi Nik
trust this finds you well. i have use the above formula and in my schedule only 100% is shown as green and rest all red and no amber. the indicator is not considering >80 % complete as amber. What could be the issue. please help thank you
Hi Khalid, Have you baselined your project and also set a project start date and a status date. Please see if my video helps any better? https://www.youtube.com/watch?v=Pl8ZW5GbB6Q&t=
thank you Nik, I followed your video step by step, im using 2016 MSP. Do you suggest any other formula for msp 2016.
Hi Nik, the formula is not work with MSP 2016. I deleted and wroteit back but stull error with [% Complete] ==>IIf([Active]=Yes,IIf([% Complete]=100,
Can we make insert RAG status to the summary task. With the above given formula is not getting implemented to summary Task.;/
Yes you can. I have created a very detailed formula, that considers milestones, summary and tasks RAG. Also Task Status calculated in between say 3 days have passed since task started which id 5 days duration, we expect that we should be around 3/5 ie 60% done by now and if it is 10% done than RED
Hi Nik, Thanks for doing this, I have followed your instructions, I am using MPP 2010, However it is not showing the image in colour represenation, it is showing complete, late, on schedule and future task, how do I represent in colour coding instead, even when I marked the image in the Graphical indicators
IIf([% Complete]>[Text1],1,(IIf([% Complete]<[Text1],2,(IIf([% Complete]=[Text1],3,1)))))
Hi you are amazing & please can you help me? I wrote this above formula but it is doesn't work in right way for all tasks… by the way the (text1) is (planned % complete) and i made it according to your video and it is work right.
wow is amazing, I am very grateful to you master. currently I am using MPP 2016.
Best regards
Emmanuel form Mexico.
Hi Nik,
I have few questions or query. If you can provide me your E-mail ID it will be very much helpful.
Thanks Krunal
Hi Nick,
Thank you for your tutorial…rally helped a lot.
However, how can I use the same formula on my sub-projects?
I applied to my sub-schedules, but doesn’t show up on the master schedule…I did it in the Master, again, doesn’t show on the sub-chedules.
Any idea?
Marjan
I copied your formula above into my MS Project 2016 file and although it took it, it shows no “red” tasks; only green and yellow. What am I doing wrong? Thanks! Susan
I also did not baseline my schedule so I am not comparing the baseline dates to the start/finish dates.
Does this hold good for MS Project Server 2019?