Stacked formula, Data, Symbol and if blank

CorrinaD

Hi, I am trying to write a formula saying:

If blank then blank

If Status column has "Full" symbol Progression to Completed Date column is "Green"

Or if Completion Date is greater than today is "Red"

Or if Completion Date is less than 7 days is "Yellow" otherwise is "Green"

So far I have this, that is working well for altering my Progression to Completed Date symbols, but unable to return the if Status is "Full" will be "Green" despite date. And blank.

Thanks heaps for any assistance.

CorrinaD

=IF([Completion Date]24 < TODAY(), "Red", IF(AND([Completion Date]24 >= TODAY(), [Completion Date]24 < TODAY(+7)), "Yellow", "Green"))

Sorry forgot to post what I have so far. :-)

Lucas Rayala

Hi @CorrinaD, I couldn't quite understand your request so I ran your question through ChatGPT and I've pasted the answer below. Using this as a starting place, let us know what's not correct about the assumptions that ChatGPT made

*** CHAT GPT RESPONSE ***

Based on your requirements, we can create the formula using nested IF statements and logical checks. Here's the modified formula to achieve what you described:

=IF(ISBLANK([Status]@row), "", IF([Status]@row = "Full", "Green", IF([Completion Date]@row = "", "",
IF([Completion Date]@row < TODAY(), "Red", IF([Completion Date]@row <= TODAY() + 7, "Yellow", "Green")))))

Let's break down the formula:

1. `IF(ISBLANK([Status]@row), "", ...)`: If the "Status" column is blank, the formula will return an empty string. This is the "If blank then blank" condition.

2. `IF([Status]@row = "Full", "Green", ...)`: If the "Status" column has the value "Full", the formula will return "Green". This takes care of the condition "If Status column has 'Full' symbol, Progression to Completed Date column is 'Green'".

3. `IF([Completion Date]@row = "", "", ...)`: If the "Completion Date" column is blank, the formula will return an empty string. This handles the scenario where you want the cell to be blank if there is no completion date.

4. `IF([Completion Date]@row < TODAY(), "Red", ...)`: If the "Completion Date" is earlier than today, the formula will return "Red".

5. `IF([Completion Date]@row <= TODAY() + 7, "Yellow", "Green")`: If the "Completion Date" is today or within the next 7 days, the formula will return "Yellow". Otherwise, it will return "Green".

By nesting these IF statements, the formula will check each condition in order and return the appropriate result based on the conditions you provided. Just replace `[Status]` and `[Completion Date]` with the actual column names you have in your Smartsheet.