Task Status Formula

[Ian Baker]

Hi
I am using the field Text1 to show a traffic light as to the current status
of a task:
GREEN - indicates a task that is currently at its correct % Complete point
or greater % Complete relative to the task's duration
AMBER - indicates a task that is currently 10% or less behind its correct %
Complete point relative to the task's duration
RED - indicates a task that is currently NOT at its correct % Complete point
or less % Complete relative to the task's duration

I have the GREEN and RED components correct in the following formula but
can't seem to work out the AMBER part of it.

IIf([% Complete]<>100,IIf(Date()>ProjDateAdd([Start],([Duration])*([%
Complete]/100),"Standard"),"Late",IIf(Date()>[Start],"OK")))

Any help is greatly appreciated!

 

[Jim Aksel]

It looks like you may have too many tests in there: If(test, true, false).
I understand the nested iif .. but it still looks like what apears after
"Late" is extraneous. Additinoally, IIf(Date()>[Start],"OK") lacks direction
.... it does not know what to return if the test is false.

Also, you should consider using [Status Date] instead of Date(). If you are
reporting status as of yesterday, your formula results would be calculatng as
of "now" instead 5PM last night. Set status date from Project/Project
Information...

Why not just use the column "Status" ? Insert that and see if it is
helpful. It will tell you late, complete, on schedule, future task. There
is also a "Status Indicator" column which will provide graphics for you. If
desired, set the graphic based on what "status" tells you.
--
If this post was helpful, please consider rating it.

Jim

Visit http://project.mvps.org/ for FAQs and more information
about Microsoft Project

 

[Ian Baker]

Thanks Jim
Unfortunately the Status column doesn't give me a "Caution" category - i.e.
when a task's % Complete is within 10% less of what it needs to be to be "On
Schedule". This is a requirement from "The Boss"

Point taken on the "what to do if False" but that was to simply show nothing
for example I could have just had a simple ""

Thanks for your help so far - greatly appreciated!

It looks like you may have too many tests in there: If(test, true, false).
I understand the nested iif .. but it still looks like what apears after
"Late" is extraneous. Additinoally, IIf(Date()>[Start],"OK") lacks direction
... it does not know what to return if the test is false.

Also, you should consider using [Status Date] instead of Date(). If you are
reporting status as of yesterday, your formula results would be calculatng as
of "now" instead 5PM last night. Set status date from Project/Project
Information...

Why not just use the column "Status" ? Insert that and see if it is
helpful. It will tell you late, complete, on schedule, future task. There
is also a "Status Indicator" column which will provide graphics for you. If
desired, set the graphic based on what "status" tells you.
--
If this post was helpful, please consider rating it.

Jim

Visit http://project.mvps.org/ for FAQs and more information
about Microsoft Project

Hi
I am using the field Text1 to show a traffic light as to the current status
of a task:
GREEN - indicates a task that is currently at its correct % Complete point
or greater % Complete relative to the task's duration
AMBER - indicates a task that is currently 10% or less behind its correct %
Complete point relative to the task's duration
RED - indicates a task that is currently NOT at its correct % Complete point
or less % Complete relative to the task's duration

I have the GREEN and RED components correct in the following formula but
can't seem to work out the AMBER part of it.

IIf([% Complete]<>100,IIf(Date()>ProjDateAdd([Start],([Duration])*([%
Complete]/100),"Standard"),"Late",IIf(Date()>[Start],"OK")))

Any help is greatly appreciated!