How to combine if statements utilizing information in multiple columns

Hello,

I currently have the following formula to automatically have a color coded dot assigned in a column depending on the due date.

=IF(ISBLANK([Due Date]@row), "", IF([Due Date]@row - TODAY() > 5, "", IF([Due Date]@row <= TODAY(), "Red", "Yellow")))

Can you help me add to this same formula an if statement that specifies:

If Status in the row is "Complete", mark at green. If it's not, complete, follow the rest of the rules above.

Best Answer

  • Forklift_Expert
    Answer ✓

    The easiest way to implement this would be to ignore the due date logic entirely. I added an IF at the beginning that makes it only run through the code you have if the status is not "Done"

    I tested this on one of my sheets and it worked

    =IF([email protected] = "Done", "Green", IF(ISBLANK([email protected]), "", IF([email protected] - TODAY() > 5, "", IF([email protected] <= TODAY(), "Red", "Yellow"))))


    For you it should look like this:

    =IF([email protected] = "Completed", "Green", IF(ISBLANK([Due Date]@row), "", IF([Due Date]@row - TODAY() > 5, "", IF([Due Date]@row <= TODAY(), "Red", "Yellow"))))

Answers

  • Forklift_Expert
    Answer ✓

    The easiest way to implement this would be to ignore the due date logic entirely. I added an IF at the beginning that makes it only run through the code you have if the status is not "Done"

    I tested this on one of my sheets and it worked

    =IF([email protected] = "Done", "Green", IF(ISBLANK([email protected]), "", IF([email protected] - TODAY() > 5, "", IF([email protected] <= TODAY(), "Red", "Yellow"))))


    For you it should look like this:

    =IF([email protected] = "Completed", "Green", IF(ISBLANK([Due Date]@row), "", IF([Due Date]@row - TODAY() > 5, "", IF([Due Date]@row <= TODAY(), "Red", "Yellow"))))

  • That is extremely helpful and worked!! Thank you so very much!!!