Stop light formula issue

[emagrrrl]

I am knew to using formulas in MS Project and am using the following formula
for a stop light field, where 4 = red (behind schedule), 3 = amber (coming
due), 2 = green (complete), and 1 = white (on schedule). The amber field is
not calculating correctly. I have tasks that are on schedule and are
scheduled to finish several months in the future, yet they are appearing as
amber. Can anyone identify where I may be having an issue with my formula?
Thanks in advance!

IIf([Duration]=0,(IIf([% Complete]=100,2,IIf([Finish]<Now()+14 And [%
Complete]<100 And [% Complete]>=80,3,IIf([Finish]<Now()+14 And [%
Complete]<80,4,1)))),IIf(([% Complete]=100 Or [%
Complete]>100*(Abs((Now()-[Start])/([Finish]-[Start])))),2,(IIf([Finish]>Now(),IIf([Start]>Now(),1,3),4))))

 

[Jack Dahlgren MVP]

Looks like it is in the last iif statement:
IIf([Start]>Now(),1,3)
So if the start is before the current date/time then the task will be amber.

I'm not sure why you have that statement in there.
You could probably rewrite it.

-Jack Dahlgren