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

 

[Robert]

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

 

[Jack Dahlgren]

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

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

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/member.php?u=53117
View this thread: http://forums.techarena.in/showthread.php?t=1001679

http://forums.techarena.in

 

[corky0770]

I'm trying to create a formula much simplier than that but for som
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

 

[Jim Aksel]

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

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: http://forums.techarena.in/microsoft-project/1001679.htm

http://forums.techarena.in