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
-
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
-
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!!!
Help Article Resources
Categories
Check out the Formula Handbook template!
