Solved

Using formulas in MS Project to indicate status of each task

Posted on 2016-08-26
4
108 Views
Last Modified: 2016-09-04
I want to add a Green, Yellow, Red status indicator to tasks in a project plan without having to do this manually.
Essentially for each tasks the rules are as follows:
1)  If the task Completion % = 100, then the task is complete and therefore Green
2)  if the task Completion % <100 and today's date is 1 day from the Finish date, then the task has the potential of being Late and therefore Yellow
3) if the task Completion % <100 and the Finish date is < today's date, the task is late and therefore Red
4) Else task is Green  

My Excel skills in writing formulas is not working for me in MS Project :(
0
Comment
Question by:jlove88
  • 2
4 Comments
 
LVL 43

Accepted Solution

by:
Jackie Man earned 500 total points
ID: 41777879
I did a quick search and the finding is as follows:


If you are using MS Project 2010 or later, the field names have changed.  Use the following formula:

IIf([Scheduled Duration]=0,(IIf([% Complete]=100,5, IIf([Scheduled Finish]<Now()+7 And [% Complete]<100 And [% Complete]>=80,3,IIf([Scheduled Finish]<Now()+7 And [% Complete]<80,4,1)))),IIf([% Complete]=100,5, IIf([% Complete]>=100*(Abs(ProjDateDiff([Scheduled Start],Now())/ProjDateDiff([Scheduled Start],[Scheduled Finish]))),2,(IIf([Scheduled Finish]>Now(),IIf([Scheduled Start]>Now(),1,3),4)))))

Source: http://blog.practicingitpm.com/2013/08/25/improved-calculated-status-indicator-ms-project/
0
 
LVL 43

Expert Comment

by:Jackie Man
ID: 41777881
In short, it is to create a custom field with the value of the formula and display the results in respective color according to the value.
0
 
LVL 18

Expert Comment

by:xtermie
ID: 41781344
You can actually add the indicators built-in MS project, which can also be customized
The process is fully described here:
https://support.office.com/en-us/article/Add-indicators-09EE6A60-E171-431A-8E1D-58ADC2A317CF?ui=en-US&rs=en-US&ad=US
0
 

Author Closing Comment

by:jlove88
ID: 41783693
Worked like a charm.  Thank you
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It was Monday morning and while heading to work those familiar feelings of frustration began to rise: How was I ever going to get my yard work done?! At the end of every weekend I discovered that I spent more time trying to decide what to work on th…
When you’re making plans to join the modern business race, you should analyze various details that may affect your results. Nowadays, millions of businesses are trying to grow into established and appreciated professional enterprises.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question