Red/Green/Yellow Formula

Ann Marie Sanders

I've tried a couple of things and I am at a loss.

I have a Planned Start Date, Planned End Date, and a % Complete column.

I would like the dots to be working as follows:

Red - When the planned start date prior to today and % complete is not = 100% or 1

Yellow - When the planned start date = today and the % complete is not = 100% or 1

All others Green

I've changed the formula several times and keep getting unparseable error.

I started with % Complete = 1, "Green", and then tried to add the others in

I've tried a lot......

This is what I have without the percent complete accounted for:

=IF([Planned End Date]@row < TODAY(), "Red", IF([Planned End Date]@row > TODAY(), "Green", IF([Planned End Date]@row = TODAY(), "Yellow")))

This works except I don't want Red if % complete = 100, etc.

I searched community and copied some formulas in and changed the fields but still get errors.

Any assistance appreciated.

Mark Safran

Hi Ann,

If I understand correctly, this is the condition you'd like to assign:

-Red if "End Date" is prior to today AND "% Complete" is less than 100%

-Yellow if "End Date" is today AND "% Complete" is less than 100%

-Green for all other conditions (i.e. "End Date" is in the future regardless of completion percentage, or "% Complete" is 100% regardless of planned end date).

Assuming that is correct, and you don't need any other exception handling for blank dates/cells, you should be able to use this:

=IF([% Complete]@row = 1, "Green", IF([Planned End Date]@row < TODAY(), "Red", IF([Planned End Date]@row = TODAY(), "Yellow", "Green")))

-MS

Ann Marie Sanders

That worked,Thank you so much. I called the help desk last week and they couldn't do it.

Mark Safran

Hi Ann,

If I understand correctly, this is the condition you'd like to assign:

-Red if "End Date" is prior to today AND "% Complete" is less than 100%

-Yellow if "End Date" is today AND "% Complete" is less than 100%

-Green for all other conditions (i.e. "End Date" is in the future regardless of completion percentage, or "% Complete" is 100% regardless of planned end date).

Assuming that is correct, and you don't need any other exception handling for blank dates/cells, you should be able to use this:

=IF([% Complete]@row = 1, "Green", IF([Planned End Date]@row < TODAY(), "Red", IF([Planned End Date]@row = TODAY(), "Yellow", "Green")))

-MS

Ann Marie Sanders

That worked,Thank you so much. I called the help desk last week and they couldn't do it.