Solved

Using formulas in MS Project to indicate status of each task

Posted on 2016-08-26
4
89 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 42

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 42

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 17

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
recommended content management solutions 6 109
Project Management tools 2 72
Home Project management software 3 114
Open soure project managemnt software 7 93
In Agile (http://en.wikipedia.org/wiki/Agile_software_development), time and again people ask this question "How would you estimate a release for a product?". When it comes from management they want to know the following: Calculate the man hours wh…
Online collaboration is quickly becoming embedded in the workplace, and its benefits are tangible. See what the current landscape looks like and what the future holds for collaboration tools and the future of work.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
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…

863 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now