Solved

Using formulas in MS Project to indicate status of each task

Posted on 2016-08-26
4
132 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 44

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 44

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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Online collaboration can help businesses be more efficient, help employees grow their skills and foster a team environment.
A simple overview of the possibilities of using technology for project management.
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…

821 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