Formula Checked Box

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!

Best Answer

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    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.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    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.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!