RAG based on % complete relative to today's date and finish 1

[GoreFish]

Hello! I've been searching far and wide for a solution to this problem and I'm striking out. What I am looking to do is have a stoplight RAG column based on a function of % Complete for a task and it its relation to today's date. Let me try to explain below:

Assumes today is 7/1/11

1 Start Finish % Complete Status
2 8/1/11 9/1/11 0% Not Started (White)
3 6/1/11 8/1/11 90% On Schedule (Blue)
4 6/1/11 8/1/11 10% Off Track (Red)
5 1/1/11 4/1/11 100% Complete (Green)

Row Detail:
2 - Not Started - This task has a future date as a start.
3 - On Schedule - This task is 30 days into a 60 day process (50%). Since 90% Complete is greater than or equal to 50% as of today's date, so it is On Schedule.
4 - Off track - Like the previous task, this is a 60 day task, but since we are at 7/1/11, we're 50% of the way through the task timeframe and only 10% there. % Complete is less than 50% of the time allocated as of today so it is Off Track.
5 - Complete - Regardless of start or end, this is at 100% complete and therefore Complete.

I know the logic is simplistic, but that's all I need for now. I can't tell you how much I appreciate the help. I was able to get this to work in Excel just fine with the following formula (I replaced cell values with English):

IF(START>TODAY(), "white"
IF(% COMPLETE=100%, "blue
IF((FINISH-START)*(% COMPLETE*1.1)>=(TODAY()-START),"green"
IF((FINISH-START)*(% COMPLETE*1.1)<(TODAY()-START),"red"

I tried engineering similar logic using MS Project expressions and came up with:

IIf([Start]>Now(),"white",IIf([% Complete]=100,"blue",IIf(([Finish]-[Start])*([% Complete]*1.1)>(Now()-[Start]),"green", IIf(([Finish]-[Start])*([% Complete]*1.1)<(Now()-[Start]),"red")))

Its not working. I think the calculation expressions are causing me grief, or maybe the "Now" function could be the problem. I am making myself crazy, so any sage/guru advice would be most welcome. Thanks in advance for any help!

-Ralph

 

[PDQBach]

First off, I'm not sure why you're using the final IIF statement. Surely, after all other choices have been rejected, the only remaining option is "Red".

I played around a bit and came up with this:

IIf([Start]>[Status Date],"white",IIf([% Complete]=100,"blue",IIf((((([Status Date]-[Start])/([Finish]-[Start]))*100)<[% Complete]),"green","red")))

You'll notice I've used [Status Date] instead of now() -- that's just a personal preference of mine (plus it made it easier to test).

If you want to use now() then:

IIf([Start]>now(),"future start",IIf([% Complete]=100,"finished",IIf(((((now()-[Start])/([Finish]-[Start]))*100)<[% Complete]),"On schedule","Late")))

(Sorry, Julie ... looks like I beat you to this one ... <grin>)

 

[JulieInMaine]

@PDQ

 

[GoreFish]

Thank you for the responses! I'm quite a rookie with expressions, so your logic stating I didn't need to use the last IIF for the "red" was a new thing for me. I took your work and added some additional logic to it. I wanted to buffer my project tasks with a positive 10% buffer in favor of being on schedule and came up with this.

What I had come up with on my own:
IIf([Start]>Now(),"white",IIf([% Complete]=100,"blue",IIf((([Finish]-[Start])*(([% Complete]*1.1)/100))>([Current Date]-[Start]),"green",IIf((([Finish]-[Start])*(([% Complete]*1.1)/100))<([Current Date]-[Start]),"red"))))

What I came up with using your tips:
IIf([Start]>[Status Date],"white",IIf([% Complete]=100,"blue",IIf((([Finish]-[Start])*(([% Complete]*1.1)/100))>([Current Date]-[Start]),"green","red"))))

I hope this helps someone else mining for solutions some day down the road.

Thanks again!

-Ralph