none
Project Pro 2010 - Formulas for Displaying Task Schedule Status Indicators in Microsoft Project RRS feed

  • Question

  • I need to indicate status in projects with a formula show status based on progress made. Below is not a bad article to get started but it relies on the finish variance field, which does not work in my view because it relies on days. For example if I set a task to complete in 7 days and I'm not showing any progress on day 6, it's green, but it's overdue if not completed in time. At the summary level, it does not work well either, because I may have overdue tasks, but the summary level is green.

    So I looked at the SV % field and that has relience on a day field as well.

    I need a formula that will tell me the schedule run-rate is on track or slipping. Any suggestions is welcomed!

    http://www.projectserverhelp.com/Lists/Posts/Post.aspx?ID=27

    Monday, October 1, 2012 9:55 AM

Answers

  • Hi Sapna, thank you for help.. I want to squeeze in a Yellow in along a green and red.  I've really looked closely at your hyperlink and noticed that the Planned % Complete Column is a formula. I've tried figuring out how that formula might be constructed. I have even tried constructing mine own before taking it to technet. Any ideas on how that Planned % Complete Column is formulated?

    thanks again!

    • Marked as answer by dovendaven1 Tuesday, October 23, 2012 5:06 PM
    Friday, October 5, 2012 4:40 AM
  • Hi,

    you do not need the formula for "Planned % Complete", this field was only added in mentioned article to demonstrate the how colors are set. But the formula will look like IIf([Duration]=0,"Milestone",Switch([Finish]<[Current Date],"100%",[Start]>[Current Date],"0%",True,(ProjDateDiff([Start],[Current Date],"Standard")/[Duration])*100 & "%")) or similar.

    Why don't you just use [Status]? Configure a field with formula "[Status]". Define Graphical Indicators for values 0, 1, 2 and 3. ANd you are done, without any Iifs, Switchs, ....

    Since you wrote in your intial question, that you are only interested in progress, you will not need an baseline related fields like Baseline Finish or Finish Variance.

    Regards
    Barbara

    • Marked as answer by dovendaven1 Tuesday, October 23, 2012 5:06 PM
    Friday, October 12, 2012 1:11 PM
    Moderator

All replies

  • Hi,

    if I got it correctly, you are looking for default field "Status". If you use this field in a formula, you can create indicators:

    • Complete (0)
    • On Schedule (1)
    • Late (2)
    • Future Task (3)

    Is this what you are looking for?
    Barbara

    Monday, October 1, 2012 10:26 AM
    Moderator
  • I second Barbara's thought, this is inbuilt and most reliable tracking mode. Let us know if you expected something different.

    Sapna Shukla

    Monday, October 1, 2012 11:44 AM
    Moderator
  • Thank you Sapna and Barbara for responding! Barbara what you outlined to ask is what I'm looking for for.

    The below formula in the previously referenced link gives returns 0,1 and 2 accurately. 3 for Future Task is not accurate. For example, when I schedule a task for 7 days, set the baseline and on the sixth day indicate 0 hours performed, it shows green.

    Additionally, group that status with other tasks that successfully show overdue at the summary level and the summary is green

    Switch(

    [% Complete]=100,"Complete",
    (([%
    Complete]<100) And ([Finish]<Date())),"Overdue",
    (([Baseline
    Start]=ProjDateValue("NA")) Or ([Baseline Finish]=ProjDateValue("NA"))),"No
    BL",
    [Finish Variance]<=0,"Green",
    [Finish
    Variance]<=(ProjDateDiff([Project Start],[Project
    Finish])*0.1),"Yellow",
    [Finish Variance]>(ProjDateDiff([Project
    Start],[Project Finish])*0.1),"Red"
    )

    Monday, October 1, 2012 6:08 PM
  • Hi,

    What Barbara and I referred to is default Status field, insert it in your Gantt Chart View, it will return the Status based on planned Vs actual % complete as On Schedule, Late, Completed or Future Task.

    You can refer for Status field here - http://epmxperts.wordpress.com/2012/04/25/track-delayed-tasks-on-a-sechedule-without-baseline/

    As for the formula you have copied ->

    Project Duration is always in working time as defined in the calendar in use in the project.  The ProjDateDiff function also uses the "Standard" calendar unless you have a task calendar applied.  A

    As you said, a task complete in 7 days and you did not show any progress on day 6, it's green -

    Reason - Formula is referring to Finish Variance that comes when you change either the Finish Date or increase the duration of task, if Finish Date remains same and duration is also not increased there is no Finish Variance.

    As you observed that even at summary level, it does not work even though you have overdue tasks, but the summary level is green.

    Reason - again the formula at summary task is also referring to Finish Variance if no over all variance found at summary level, irrespective of sub-tasks status it will be Green.


    Sapna Shukla

    Tuesday, October 2, 2012 4:34 PM
    Moderator
  • Hi Sapna, thank you for help.. I want to squeeze in a Yellow in along a green and red.  I've really looked closely at your hyperlink and noticed that the Planned % Complete Column is a formula. I've tried figuring out how that formula might be constructed. I have even tried constructing mine own before taking it to technet. Any ideas on how that Planned % Complete Column is formulated?

    thanks again!

    • Marked as answer by dovendaven1 Tuesday, October 23, 2012 5:06 PM
    Friday, October 5, 2012 4:40 AM
  • Hi,

    you do not need the formula for "Planned % Complete", this field was only added in mentioned article to demonstrate the how colors are set. But the formula will look like IIf([Duration]=0,"Milestone",Switch([Finish]<[Current Date],"100%",[Start]>[Current Date],"0%",True,(ProjDateDiff([Start],[Current Date],"Standard")/[Duration])*100 & "%")) or similar.

    Why don't you just use [Status]? Configure a field with formula "[Status]". Define Graphical Indicators for values 0, 1, 2 and 3. ANd you are done, without any Iifs, Switchs, ....

    Since you wrote in your intial question, that you are only interested in progress, you will not need an baseline related fields like Baseline Finish or Finish Variance.

    Regards
    Barbara

    • Marked as answer by dovendaven1 Tuesday, October 23, 2012 5:06 PM
    Friday, October 12, 2012 1:11 PM
    Moderator
  • got it to work! thx!
    Saturday, October 13, 2012 12:05 AM