none
Project Web App 2013 Graphical Indicator help RRS feed

  • Question

  • Good day to all. I thought I had another post but am unable to find so I apologize if I am repeating. I have the following formula that creates the status and appropriate indicator in my project schedule. This formula works correctly for about 90% of my needs but there is one status that I simply cannot figure out.

    What we are looking for is as follows:

    Complete =% Complete = 100%

    Late = 1 day a past finish

    On Schedule = within timeframe

    At Risk - still within timeframe but needs attention

    Not Started = % Complete 0 and Finish is >/= today

    Future Task = % Complete 0 and Start date is 7 days or more past today

    On Hold - simply if certain field is marked 1.

    Everything about this formula works EXCEPT the "future task' status and I am sure you have guessed by now, it conflicts with "not started"  I am struggling with defining the difference in the 2.

    Here is my current formula:

    IIf([Hold Task] = 1, "On Hold", IIf([% Complete] = 0 And [Finish] >= Now(), "Not Started", IIf([Start] > Now() And [% Complete] = 0, "Future Task", IIf([% Complete] = 100, "Complete", IIf([% Complete] < 100 And [Finish] <= Now(), "Late", IIf([% Complete] < 100 * ProjDateDiff(Now(), [Start]) / ProjDateDiff([Finish], [Start]), "At Risk", "On Schedule"))))))

    As you can see by the image, everything reads as expected except the 2 dates that SHOULD be Future Task based on our needs.  Any help is GREATLY appreciated!

    Schedule screenshot

    Monday, November 13, 2017 8:32 PM

Answers

  • Hello Tonya,

    I was able to reproduce your error in my local project custom field formula.  When I rearranged the order of the fields it seemed to work. Not sure why. Maybe try that?

    Example:

    IIf([Start]>Now() And [% Complete]=0,"Future Task",IIf([Number1]=1,"On Hold",IIf([% Complete]=0 And [Finish]>=Now(),"Not Started",IIf([Start]>Now() And [% Complete]=0,"Future Task",IIf([% Complete]=100,"Complete",IIf([% Complete]<100 And [Finish]<=Now(),"Late",IIf([% Complete]<100*ProjDateDiff(Now(),[Start])/ProjDateDiff([Finish],[Start]),"At Risk","On Schedule")))))))


    JH Blake

    • Marked as answer by Tonya Reznor Tuesday, November 14, 2017 12:03 PM
    Tuesday, November 14, 2017 12:06 AM

All replies

  • Hello Tonya,

    I was able to reproduce your error in my local project custom field formula.  When I rearranged the order of the fields it seemed to work. Not sure why. Maybe try that?

    Example:

    IIf([Start]>Now() And [% Complete]=0,"Future Task",IIf([Number1]=1,"On Hold",IIf([% Complete]=0 And [Finish]>=Now(),"Not Started",IIf([Start]>Now() And [% Complete]=0,"Future Task",IIf([% Complete]=100,"Complete",IIf([% Complete]<100 And [Finish]<=Now(),"Late",IIf([% Complete]<100*ProjDateDiff(Now(),[Start])/ProjDateDiff([Finish],[Start]),"At Risk","On Schedule")))))))


    JH Blake

    • Marked as answer by Tonya Reznor Tuesday, November 14, 2017 12:03 PM
    Tuesday, November 14, 2017 12:06 AM
  • As an aside a much easier way to read this would be to use a switch statement.

    Switch(

    IIf([Start]>Now() And [% Complete]=0,"Future Task",

    IIf([Number1]=1,"On Hold",

    ",IIf([% Complete]=0 And [Finish]>=Now(),"Not Started",

    etc...

    )

    Also, you wanted future tasks to be future tasks to be 7 where the start is 7 days or more past today... Assuming a standard calendar you would use 5 working days and the formula would be...

    IIf(([Start]>projdateadd(Now(), 5 * [minutes per day])) And [% Complete]=0,"Future" 

    IIf([Start]>projdateadd(Now(),5*[Minutes Per Day]) And [% Complete]=0,"Future")

    Also, worth checking the status field, it is automatically set and gives you "complete" (note you have to work out the numeric value for status.


    Ben Howard [MVP] | web | blog | book

    Tuesday, November 14, 2017 7:33 AM
    Moderator
  • Thank you SO much! This worked exactly as I needed!
    Tuesday, November 14, 2017 12:03 PM