Health Column

tsandluck

Hi Team,

I'm using a formula to track the health of all tasks and using the standard "Green", "Yellow" and "Red" symbols to show at risk items. I've used the sheet as a template and new date ranges are not yet filled out, but the symbol Red shows up. How do I get this to go away until the task is actually at risk?

Thank you

KPH

Hi @tsandluck

The color of the dot is controlled by a formula within the cell. This is probably an IF formula. If you double-click on the cell you will be able to see and edit the formula (unless it is a column formula, in which case you need to right-click and select "Edit column formula").

To change how the colors are displayed you need to change the formula.

If it is showing as red when there is no date, it could be that "red" is the default response if the logic to turn it green or amber is not true. You can either edit the part that turns the red on, or you can switch the entire formula off for blank dates by adding another IF at the start to say if the date is blank, do nothing. It's hard to help precisely without seeing your sheet or formula. The additional step to stop the formula from doing anything if the date is blank would be:

=IF(ISBLANK(Date@row), "", original formula)

This means if the cell in the current row in the Date column is blank return "" (essentially nothing). If not then do whatever the original formula says.

Note - you need to have the closing parenthesis at the end, after any parentheses that are in your original formula. You can change the column name of Date to whatever your date column is called. If this has spaces, you need to put [ and ] around the name, for example:

=IF(ISBLANK([State Date]@row), "", original formula)

I hope that helps.

tsandluck

That worked, thank you very much!

KPH

Hi @tsandluck

The color of the dot is controlled by a formula within the cell. This is probably an IF formula. If you double-click on the cell you will be able to see and edit the formula (unless it is a column formula, in which case you need to right-click and select "Edit column formula").

To change how the colors are displayed you need to change the formula.

If it is showing as red when there is no date, it could be that "red" is the default response if the logic to turn it green or amber is not true. You can either edit the part that turns the red on, or you can switch the entire formula off for blank dates by adding another IF at the start to say if the date is blank, do nothing. It's hard to help precisely without seeing your sheet or formula. The additional step to stop the formula from doing anything if the date is blank would be:

=IF(ISBLANK(Date@row), "", original formula)

This means if the cell in the current row in the Date column is blank return "" (essentially nothing). If not then do whatever the original formula says.

Note - you need to have the closing parenthesis at the end, after any parentheses that are in your original formula. You can change the column name of Date to whatever your date column is called. If this has spaces, you need to put [ and ] around the name, for example:

=IF(ISBLANK([State Date]@row), "", original formula)

I hope that helps.

tsandluck

That worked, thank you very much!

KPH

That's great, I'm glad I could help.

If you can, please mark the answer as accepted so anyone else looking for the same thing can find it.