Ask a new question

Formula to display a graphical indicator in MS Project 2016

Hi,

I am trying to update a formula for a graphical indicator that I am using in a project file. The indicator calculates:

  • tasks that are 100% complete, shows a green check mark
  • tasks that have a finish date 30, 60, or 90 days from the current date, show a red, yellow or green stoplight (see the picture at the bottom and the formula below)

IIf([% Complete]=100,"Complete",IIf(DateDiff("d",[Current Date],[Finish])<=30,"30",IIf(datediff("d",[Current Date],[Finish])<=60,"60",IIf(datediff("d",[Current Date],[Finish])<=90,"90",0))))

I am trying to add an element that accounts for past due tasks, or a task where the finish date past the current date.  I found the following formula online, but I have not been able to correctly modify it to calculate past due tasks, or combine it with the formula above to make one indicator. Thanks in advance for any suggestions!!

Switch(Date()<=[Finish] And [% Complete] <100,”Should Have Finished”, Date ()<=[Start],””, Date()<=[Finish],"")


Answer
Answer

Please try this formula:

Switch(

[% Complete]=100,"Complete",

DateDiff("d",[Current Date],[Finish])<=0,"Should Have Finished",

DateDiff("d",[Current Date],[Finish])<=30,"30",

DateDiff("d",[Current Date],[Finish])<=60,"60",

DateDiff("d",[Current Date],[Finish])<=90,"90",

True,"--")

If you right click on the image and open it in a new tab, browser will display it in full size.

Please note that I did not evaluate the logic of the formula, it is up to you. The DateDiff formula returns negative results if the current date is later than the finish date. 

3 people found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info

Last updated January 9, 2024 Views 2,050 Applies to: