How to combine if statements utilizing information in multiple columns

nancyp · 03/14/22

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.

Forklift_Expert · 03/14/22

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(Status@row = "Done", "Green", IF(ISBLANK(Deadline@row), "", IF(Deadline@row - TODAY() > 5, "", IF(Deadline@row <= TODAY(), "Red", "Yellow"))))

For you it should look like this:

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

Forklift_Expert · 03/14/22

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(Status@row = "Done", "Green", IF(ISBLANK(Deadline@row), "", IF(Deadline@row - TODAY() > 5, "", IF(Deadline@row <= TODAY(), "Red", "Yellow"))))

For you it should look like this:

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

nancyp · 03/14/22

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

How to combine if statements utilizing information in multiple columns

Best Answer

Answers

Help Article Resources

Categories