From: emagrrrl on
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))))
From: Jack Dahlgren MVP on
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

"emagrrrl" <emagrrrl(a)discussions.microsoft.com> wrote in message
news:A1BE255E-A794-4331-9801-08FFA145FFE7(a)microsoft.com...
> 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))))