Solved

Using formulas in MS Project to indicate status of each task

Posted on 2016-08-26
4
355 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 47

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 47

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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

If you are using Scrum Framework or another agile process, a retrospective may be part of it. Does your team perform retrospectives? Are you getting value from your retrospectives? I see a common anti-pattern when people conduct a retrospective f…
In this article, you will read about the trends across the human resources departments for the upcoming year. Some of them include improving employee experience, adopting new technologies, using HR software to its full extent, and integrating artifi…
Simple Linear Regression
Starting up a Project

632 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