How do I use IF formula to change a colour based on a percentage column?

Tania Cubell

P.S. -- it removes the brackets around [Finish] and not certain why ?

Tania Cubell

Paul Newcome

The square brackets around a column name are only used when the column name contains a space, special character, number, or any combination thereof. If it does not contain anything other than a single text string, then the square brackets are not required and SS automatically removes them.

Double check the column properties for the Finish column and make sure it is set to a date type column.

If it is set to a date type of column, how exactly are the dates entered into the column?

Tania Cubell

@Paul Newcome That was an amazing journey!!! Thank you for all your help -- very fun puzzle solve :)

it turns out the decimal for %ages had been replaced with a comma --- data type i had checked previously so it was a bit confusing until i looked and looked and had the Aha moment :)

=IF([%Complete]@row = 1, "Blue", IF(AND(Finish@row <= TODAY(3), [%Complete]@row < 0.51), "Red", IF(AND(Finish@row <= TODAY(3), [%Complete]@row < 0.8), "Yellow", "Green")))

Paul Newcome

Happy to help. 👍️

Arama Eruera

Apologies for the delayed response. Thank you for all your help - worked a treat once I put a space in between the % complete as per column label. Cheers

Deepa SP

This is brilliant. I am trying to use this formula in my sheet. Out of curiosity, does the cell change colour or does it just list the colour

Paul Newcome

@Deepa SP It depends on your column type. If it is a symbol column set to RYGB it will display the appropriately colored dot. Any other column type will display the text. To change the color of the cell itself you would need to incorporate Conditional Formatting.

Deepa SP

Thanks Paul. I am not able to use much of conditional formatting as its getting very complicated and hence went down the formula route. I will try using the RYGB drop down buttons.

Deepa SP

@Paul Newcome When I try to use a formula in the symbol column, I keep getting a message prompt that "The column is restricted to symbols" and then returns an #UNPARSEABLE. What am I doing wrong here?

Paul Newcome

@Deepa SP It depends on your column settings and the formula you are using.

Deepa SP

The column is set of symbols (RYGG). What I am trying to do is to choose the option Grey if a particular month is not required, but I need this only for cells that are not blank ( i.e) populated. I think I am getting the formula wrong when trying to find if a cell is not blank.

The formula here is to find out if a cell is not blank and if does not have July ( of the 4 options), then the cell needs to choose the Grey option.

=IF(NOT(ISBLANK([Systems - Required (next 3 months)]1)) AND [Systems - Required (next 3 months)]1<>July,"Grey",[" "])

Paul Newcome

@Deepa SP

=IF(AND([Systems - Required (next 3 months)]@row <> "", [Systems - Required (next 3 months)]@row <> "July"), "Grey")

Deepa SP

@Paul Newcome : Thank you . Just tried that and I get a message that its Invalid operation.

Paul Newcome

Can you copy/paste the formula exactly as it is directly from the sheet?