Formula Explanation

rachkh

Can someone help me break out my formula below. I think I am missing something.

=IF([% Complete]@row = 1, "Green", IF([Start Date]@row > TODAY(), "Green", IF(AND([End Date]@row <= TODAY(), [Start Date]@row < TODAY()), "Red", IF(NOT([Start Date]@row > TODAY(+7)), "Yellow", IF([Start Date]@row <= TODAY(), "Green")))))

Formula:

=IF([% Complete]@row = 1, "Green"

Explanation:

IF([Start Date]@row > TODAY(), "Green"

Explanation:

IF(AND([End Date]@row <= TODAY(), [Start Date]@row < TODAY()), "Red"

Explanation:

IF(NOT([Start Date]@row > TODAY(+7)), "Yellow"

Explanation:

IF([Start Date]@row <= TODAY(), "Green")))))

Explanation:

Mark Safran

Hi @rachkh

No problem!

Assuming "due date" in your description here is your "End Date" column from above, the following should work for what you're trying to achieve:

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

(You'll see that we're not even evaluating against the start date column anymore. Green is the default color, and we're only changing to Red or Yellow based on the due date (aka End Date), so we can just use the value_if_false to resolve anything else).

-MCS

Mark Safran

Hi @rachkh

This formula would live in a "Symbol" type column, and determines the color of the Harvey Ball based on the conditions of up to three other columns on your sheet. The nature of the nested IF( ) statements also means that it will only go on to evaluate the next conditional if the previous one is not met (aka if the condition is false). Therefore, the order of these is important.

=IF([% Complete]@row = 1, "Green"

Explanation: If you are 100% Complete, make the Harvey Ball green, regardless of any other column values. Only move on to the next conditional if "% Complete" is less than 100%.

IF([Start Date]@row > TODAY(), "Green"

Explanation: If the Start Date is in the future (aka "greater than today"), make the Harvey Ball green, regardless of any other columns. Only move on to the next step if the "Start Date" is the current date or older.

IF(AND([End Date]@row <= TODAY(), [Start Date]@row < TODAY()), "Red"

Explanation: If the End Date is the current date or older (aka not in the future) AND the Start Date is older than the current date, make the Harvey Ball red. Both conditions have to be true for this to resolve. Only move on to the next step if one or both of these date comparisons are false.

IF(NOT([Start Date]@row > TODAY(+7)), "Yellow"

Explanation: If the Start Date is not more than one week in the future (aka within the next 6 days), make the Harvey Ball yellow. Only move on to the next step if the the start date is the current date or older.

NOTE: This condition will always fail in its current order/placement in the formula, and you will never get a symbol to resolve to yellow. This is because your second IF( ) statement resolves the Harvey Ball to green if if the date is at any point in the future, so there are no future dates left for it to resolve to any other color. You should move this condition above the '[State Date]>Today( )' one if you want this to resolve.

IF([Start Date]@row <= TODAY(), "Green")))))

Explanation: If the Start Date is today or earlier, make the Harvey Ball green, regardless of any other columns.

NOTE: This seems a bit out of place as well, and could be simplified. You also don't have a final state to turn the Harvey Ball if all conditions fail, which is OK, but again could be used to simplify the overall formula.

Looking at the formula as a whole, "Green" seems to be the color that you resolve to the most frequently. You only resolve to Red if the End Date is in the past, and you only resolve to Yellow if the Start Date is within the next 6 days. Any other condition (or combination of conditions) will resolve to Green. Given that, you might find it easier to simplify this formula. If you can write out in plain text what conditions you want to resolve to what colors, we can help rewrite the formula.

rachkh

Thank you @Mark Safran! I appreciate your explanations, they are SUPER helpful!

This is what I want the formula to return:

a.    if % Complete column is 100% = green

b.    if due date or after = red

c.    if 1 week before due date = yellow

d.    If start date is in the future = green (default)

Mark Safran

Hi @rachkh

No problem!

Assuming "due date" in your description here is your "End Date" column from above, the following should work for what you're trying to achieve:

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

(You'll see that we're not even evaluating against the start date column anymore. Green is the default color, and we're only changing to Red or Yellow based on the due date (aka End Date), so we can just use the value_if_false to resolve anything else).

-MCS

rachkh

Thanks again @Mark Safran! Seriously made my day. 😀