Nest IF AND OR multiple layers to get a RED or GREEN Result/Status

Garry

This is my challenge below. I spelled it out in the screen shot. I need an overall status of Red or Green based on duration correlated to phase status.... Looking for help:

Heather Duff

Hi Garry,

I may be oversimplifying this, but is it true that if any of the phases are marked red, the overall status will be red?

If so, it would be fairly simple:

=if([phase 1]@row="Red","Red",if([phase 2]@row="Red","Red",if([phase 3]@row="Red","Red",if([phase 4]@row="Red","Red","Green")

This basically translates to checking each column to see if it is marked red, and if it is, display red in the overall column; if not, display green.

Hope this helps! Let me know if it truly is more complicated than that, and I'll see what I can do.

Best,

Heather

Garry

Hi, thanks, it is more complicated than the above. The above only cares if the Phase is red or green, but it doesn't take into account timing.

A phase is green if all the components of that phase have been checked off. So say Phase 1 has three tasks/components, if all checked the Phase 1 column is then Green. However, we want all three of those done with the first 60 days. So if they are all green but not until day 90... from 61-90 the overall project is now RED. So time must be included in the overall status. Or the individual Phases, need timing added to them.