Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Using formulas in MS Project to indicate status of each task

Posted on 2016-08-26
4
Medium Priority
?
584 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 48

Accepted Solution

by:
Jackie Man earned 2000 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 48

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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Agile and Scrum have almost become synonymous. Have you wondered what's the difference? Scrum is just one way to be Agile. It is the most popular which leads to the common confusion. Agile actually refers to a philosophy shared by group of developme…
Transparency shows that a company is the kind of business that it wants people to think it is.
Simple Linear Regression
Introduction to Processes

719 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