RAG status - formula required

[msprojectuser]

Hello

I am trying to write a formula that will show a RAG status (Red/Amber/Green and Complete) in a custom field in MS Project, based on the following criteria:

1. If the task is complete then it shows a tick graphical indicator. and if the task has completed ahead of time (based on the finish date) then it shows a green happy smiling face graphical indicator.

2. If the task is incomplete and todays date has passed/ the task finish date is less than todays date (i.e. the task has slipped) then it shows a Red graphical indicator. and If the task has slipped by more than 3 weeks it shows an unhappy red face.

3. If the task is incomplete (and less than 75% completed) and there are 7 days or less left until the finish date then it shows a Amber graphical indicator.(i.e. warning task may slip)

4. If the task is incomplete and there are more than 7 days left until the finish date then it shows a green graphical indicator. (i.e. task is on track)

I would then select the graphical indicators whereby:
complete is C
red is R
amber is A
green is G

Im not at all technical so have started writing a formula, but its very basic as i dont know how to write a formula to do the above:-

IIf([% Complete]=100,"C",IIf([% Complete]<100 And [Finish]<[Current Date],"R",IIf([% Complete]<100 And [Finish]>[Current Date],"G")))

Please could you help??

many thanks

[Landon]

You are too close but your formula is not complete I think. It should be bit more long. The beginning of that formula should be like this (it responds to the § 1 and 2):

IIf([% Complete]=100 And [Actual Finish]=[Baseline Finish],"1a",IIf([% Complete]=100 And [Actual Finish]<[Baseline Finish],"1b",IIf([% Complete]<100 And [Finish]<[Current Date] And ProjDateDiff([Baseline Finish],[Finish])/[Minutes Per Day]<=15,"2a",IIf([% Complete]<100 And [Finish]<[Current Date] And ProjDateDiff([Baseline Finish],[Finish])/[Minutes Per Day]>15,"2b"))))

[Jason A]

I think that the custom field can be simplified by removing the redundant checks for % complete. You can also pull out one of the [Finish] < [Current Date] terms

[corky0770]

I'm trying to create a formula much simplier than that but for some reason i keep getting a syntax error. here's what i want to do:

1 = task complete
2 = past due
3 = task is due in less than 7 days
4 = task is due in greater than 7 and less than 14 days
5 = task is due in more than 14 days

this is the formula i started..

IIf( [% Complete] = 100,1 ),IIf( [Finish] < 7,3 ),IIf( [Finish] < [Status Date] ,2 ), IIf( [Finish] > 7AND < 14,4), IIf( [Finish] > 14,5)