Formula using both date and %complete

[jrunner]

I want to use a column that is a graphical light indicator based on current
date, start date, and %complete. I've read other questions on this thread,
and read the info provided in links on how to setup custom fields, however,
my current formula is not working correctly.

I want to use an indicator which changes color based on the following:

if [(current date - 5d) > current date] and (%complete <100) then "red"

if [(current date - 5d) <= current date] or (%complete = 100) then "green"

I've tried this formula, but sub-task graphical indicators don't always
appear:

IIf([% Complete]<100 And ([Start]<ProjDateValue([Current
Date]-5)),"RED",IIf([% Complete]=100 or ([Start]>=ProjDateValue([Current
Date]-5)),"GREEN")

Thanks for you help.
Jeff

 

[Gérard Ducouret]

Hello Jeff,
Does this formula work as you expect ?
IIf([Current Date]-5>[Finish] And [% complete]<100,"Red",IIf([Current
Date]-5<[Finish] And [% Complete]=100,"GREEN","YELLOW"))
Hope this helps,

Gérard Ducouret

 

[JR]

Thanks Gérard. This formula was added without the syntax error I was
receiving before!! A minor side effect showed up, the graphic indicator whose
color is assigned based on the formula value (eg. RED, GREEN, YELLOW) is only
displaying for high level tasks - not subtasks. Is this a bug? I've seen this
before, and I'm wondering if three formula values are not supported, maybe
only two. I appreciate your feedback.

Regards,
Jeff

Hello Jeff,
Does this formula work as you expect ?
IIf([Current Date]-5>[Finish] And [% complete]<100,"Red",IIf([Current
Date]-5<[Finish] And [% Complete]=100,"GREEN","YELLOW"))
Hope this helps,

Gérard Ducouret

I want to use a column that is a graphical light indicator based on current
date, start date, and %complete. I've read other questions on this thread,
and read the info provided in links on how to setup custom fields, however,
my current formula is not working correctly.

I want to use an indicator which changes color based on the following:

if [(current date - 5d) > current date] and (%complete <100) then "red"

if [(current date - 5d) <= current date] or (%complete = 100) then "green"

I've tried this formula, but sub-task graphical indicators don't always
appear:

IIf([% Complete]<100 And ([Start]<ProjDateValue([Current
Date]-5)),"RED",IIf([% Complete]=100 or ([Start]>=ProjDateValue([Current
Date]-5)),"GREEN")

Thanks for you help.
Jeff

 

[JR]

Sorry, never mind. I did not change my column to a "text" field. Once I
changed this, it worked great.

Thanks again, Jeff

Hello Jeff,
Does this formula work as you expect ?
IIf([Current Date]-5>[Finish] And [% complete]<100,"Red",IIf([Current
Date]-5<[Finish] And [% Complete]=100,"GREEN","YELLOW"))
Hope this helps,

Gérard Ducouret

I want to use a column that is a graphical light indicator based on current
date, start date, and %complete. I've read other questions on this thread,
and read the info provided in links on how to setup custom fields, however,
my current formula is not working correctly.

I want to use an indicator which changes color based on the following:

if [(current date - 5d) > current date] and (%complete <100) then "red"

if [(current date - 5d) <= current date] or (%complete = 100) then "green"

I've tried this formula, but sub-task graphical indicators don't always
appear:

IIf([% Complete]<100 And ([Start]<ProjDateValue([Current
Date]-5)),"RED",IIf([% Complete]=100 or ([Start]>=ProjDateValue([Current
Date]-5)),"GREEN")

Thanks for you help.
Jeff