Help with an existing RYGB Formula

LLaCosta

The start date is the "future date"

I have a target % complete column that contains this formula:

=IFERROR(IF(Status@row <> "Completed", IF(NETDAYS([Today (hidden)]$1, [Planned End]@row) / NETDAYS([Planned Start]@row, [Planned End]@row) > 1, "Future", IF(NETDAYS([Today (hidden)]$1, [Planned End]@row) / NETDAYS([Planned Start]@row, [Planned End]@row) < 0, "Late", IF([Planned Start]@row = [Today (hidden)]$1, 1 / NETDAYS([Today (hidden)]$1, [Planned End]@row), 1 - (NETDAYS([Today (hidden)]$1, [Planned End]@row) / NETDAYS([Planned Start]@row, [Planned End]@row))))), 1), "Missing Dates")

And yes, if the Target % complete formula produces the word "future", I would want the health to show green until 100% where it turns blue

Let me know if you want a screen shot of the sheet with that formula from the target % complete column.

Paul Newcome

So if the [Start Date] is in the future then the [Target % Complete] will show "Future". If the [Target % Complete] is "Future" then any [% Complete] under 100% should be "Green". If that is the correct logic, then we should be able to get things working for you with the below modification...

=IF([% Complete]@row = 1, "Blue", IF(OR([% Complete]@row >= [Target % Complete]@row, [Target % Complete]@row = "Future"), "Green", IF(OR(ISBLANK([Target % Complete]@row), [% Complete]@row * 2 < [Target % Complete]@row), "Red", IF([% Complete]@row < [Target % Complete]@row, "Yellow"))))