# Project Web App 2013 Graphical Indicator help • ### 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! Monday, November 13, 2017 8:32 PM

• 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 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 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([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
• Thank you SO much! This worked exactly as I needed!
Tuesday, November 14, 2017 12:03 PM