Formula that changes status symbol based on check box checked/un-checked

Anthony Nicholas

I'm trying to create a formula that changes my status column to the red or green symbols.

I looked at some examples on the site, but wasn't able to get it to work on my sheet. I was trying to set up a formula around the following parameters..

If check box is checked then green

If check box is un-checked then red

I have used =IF([Renewal Date (Enter next Renewal Date)]@row > TODAY(), "Green", IF([Renewal Date (Enter next Renewal Date)]@row < TODAY(), "Red", "Yellow")) based on date column.

Thank you in advance for your help

Kelly Moore

Hey @Anthony Nicholas

Are you looking to have the status changed based solely on the checkboxes, or are you trying to add it to the formula with the dates?

To use solely with a checkbox, place this formula in the status column:

=IF([your checkbox column]@row=1, "Green", "Red")

you will need to update the formula above with your column name

Will this work for you? If you are trying to add this to the other date criteria, please let me know if the condition for the checkbox is an AND condition (both must be true) or an OR condition (either is true). I'd be happy to help.

Kelly

Anthony Nicholas

That worked great! Thank you

However after more thought, if I wanted Yellow for column 1 if checked, Green for column 2 if checked and Red for column 2 if un-checked. Is that possible?

Thank you again

Kelly Moore

Hey @Anthony Nicholas

Yes, it's doable. And I need some clarification please- your definition for Yellow of Col1 offers challenges. The examples below show the 4 different possible combinations of your two checkbox columns. Could you clarify what color you would expect each of these examples to show? For example, in #2, is it Yellow, or is it Red? And #4? That will inform me of what priority you place of each example, which is important when building an IF statement

Anthony Nicholas

Hi @Kelly Moore

#1 Red (for no check mark on col 1) Col 2 would not be checked if col 1 is not checked

#2: Yellow (for only col 1 checked)

#3: Col 1 would always be checked first before column 2 is checked

#4: Green (for both checked)

Col 1 would act as the Completed Task check mark

Col 2 would act as the Reviewed check mark

Kelly Moore

Hey

Thanks, just what I needed

=IF([Col 2]@row = 1, "Green", IF([Col 1]@row = 1, "Yellow", "Red"))

Will that work for you?

Kelly