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(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"))))
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(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"))))
-
That is extremely helpful and worked!! Thank you so very much!!!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- 10.8K Get Help
- 65 Global Discussions
- 69 Industry Talk
- 385 Announcements
- 3.5K Ideas & Feature Requests
- 55 Brandfolder
- 125 Just for fun
- 50 Community Job Board
- 464 Show & Tell
- 40 Member Spotlight
- 44 Power Your Process
- 28 Sponsor X
- 234 Events
- 7.3K Forum Archives
Check out the Formula Handbook template!