Formula Checked Box

Marcos Aguilar

Could you please help me to edit a formula?

There are 3 columns, Start Date, End Date and Completed (this is a check box)

I have a health column where the symbols change:

if a date is between today and the due date = Green

if the date is 3 days before the due date from today, then turn Yellow

if the date is on or past due date from today = Red

This is the formula:

=IF(ISBLANK([Start Date]@row), "", IF(AND([DUE DATE]@row > TODAY(), [DUE DATE]@row <= TODAY(3)), "Yellow", IF([DUE DATE]@row > TODAY(), "Green", IF([DUE DATE]@row <= TODAY(), "Red", ""))))

But my question is how can I edit the formula to add the Completed column making the symbol change to blue if the column Completed is checked?

Thank you so much and any help would be greatly appreciated!

Jeff Reisman

If your intent is to have the Completed checkbox override the other values, then you want to put it before all the other colors. First, though, make sure your column format is set for RYGB:

=IF(ISBLANK([Start Date]@row), "", IF(Completed@row = 1, "Blue", IF(AND([DUE DATE]@row > TODAY(), [DUE DATE]@row <= TODAY(3)), "Yellow", IF([DUE DATE]@row > TODAY(), "Green", IF([DUE DATE]@row <= TODAY(), "Red", "")))))

Don't forget to add one more end parentheses at the end of the formula.

Jeff Reisman

If your intent is to have the Completed checkbox override the other values, then you want to put it before all the other colors. First, though, make sure your column format is set for RYGB:

=IF(ISBLANK([Start Date]@row), "", IF(Completed@row = 1, "Blue", IF(AND([DUE DATE]@row > TODAY(), [DUE DATE]@row <= TODAY(3)), "Yellow", IF([DUE DATE]@row > TODAY(), "Green", IF([DUE DATE]@row <= TODAY(), "Red", "")))))

Don't forget to add one more end parentheses at the end of the formula.