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

Hello,
your formula will be quite long. Here is the beginning of this formula : 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"))))

You will have to continue this logic for the other tests :
Insert a Text(x) field : Insert / Column
Right click on that field : Customize Fields
Click the formula button
Paste the above formula...
then set the Graphical indicators as you want...

Gérard Ducouret



"msprojectuser" <msprojectuser.3cn4ne@DoNotSpam.com> a écrit dans le message
de news: msprojectuser.3cn4ne@DoNotSpam.com...
>
> 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
>
>
> --
> msprojectuser
> ------------------------------------------------------------------------
> msprojectuser's Profile: http://forums.techarena.in/members/53117.htm
> View this thread: RAG status - formula required
>
> http://forums.techarena.in
>

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 but I have not done it below.

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

-Jack Dahlgren

"Robert" wrote:

> Hello,
> your formula will be quite long. Here is the beginning of this formula : 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"))))
>
> You will have to continue this logic for the other tests :
> Insert a Text(x) field : Insert / Column
> Right click on that field : Customize Fields
> Click the formula button
> Paste the above formula...
> then set the Graphical indicators as you want...
>
> Gérard Ducouret
>
>
>
> "msprojectuser" <msprojectuser.3cn4ne@DoNotSpam.com> a écrit dans le message
> de news: msprojectuser.3cn4ne@DoNotSpam.com...
> >
> > 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
> >
> >
> > --
> > msprojectuser
> > ------------------------------------------------------------------------
> > msprojectuser's Profile: http://forums.techarena.in/members/53117.htm
> > View this thread: RAG status - formula required
> >
> > http://forums.techarena.in
> >
>
>
>

[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)

You will need to use the project date difference function. Here's a few
items to help get you started:

ProjDateDiff(Now(),[Finish])/[Minutes Per Day]
Calculates the difference in days between Now and the finish date. The
order of the arguements is important so the sign of the result is correct.
You want + to be in the future and minus to be in the past.


So your first test becmes:
ProjDateDiff(Now(),[Finish])/[Minutes Per Day]<7

However, remember that a late task will also pass that test since -4 is less
than 7.

You can solve that by testing that datediff is also positive using an AND
test:

AND(ProjDateDiff(Now(),[Finish])/[Minutes Per
Day]<7,ProjDateDiff(Now(),[Finish])/[Minutes Per Day]>=0)

Your nested "IF" will get huge and you have a limit of 255 characters.

You might try using a Switch Statement. I would go at it like this:

If(%Complete=100,1, if([finish]<[Status Date],2,Switch(xxxxxxxxx)

The order of your tests is going to be important. Logically think about what
you will know when you finally get to a certain test (if statement)

There is probably an easier solution. If you insert the "Status" column, it
will tell you "Complete", "On Schedule", "Late", or "Future Task" based on
the status date.

You can then limit your formula to only tasks in the future. However, I
would just use the auto filter with %Complete<100% and Finish would be custom
and set to either 7 or 14 days in the future.

There are also filters for Date Range and Incomplete tasks.

ALthough I think I know what you are getting at, RAG is not really used in
this way. RAG is ordinarily used for In process tasks comparing desired
progress against actual progress. Yes, a late task should show "red" ... but
in my world we would not code tasks due to be complete in the near future as
Yellow or Green. For example a task that should have started yesterday and
is due in 3 days, what color is that? I say red, your formula does not
address this. Of course, logic tells us we should move that start date to
the future.

I have started some comments on this in my blog. Hit the link at the
bottom. When the blog opens, select MS Proejct Tips on the left side.

Think about this for RAG: You want to compare where you are with where you
want to be.

Personally, on these tests you are running, I would use a colored icon and
leave it blank for complete tasks and tasks due more than 14 days out, red
for late tasks, and then maybe two other colors for due within 7 and due
within 14.

--
If this post was helpful, please consider rating it.

Jim Aksel, MVP

Check out my blog for more information:
http://www.msprojectblog.com



"corky0770" wrote:

>
> 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)
>
>
> --
> corky0770
> ------------------------------------------------------------------------
> corky0770's Profile: http://forums.techarena.in/members/corky0770.htm
> View this thread: RAG status - formula required
>
> http://forums.techarena.in
>
>