Help with Nested If Function
Hi,
I want to change the Health column based on several conditions; If the due date is past due, red; if the due date is less than 5 days away and % complete is 0, if the due date is less than 5 days away and % complete is 50% or less then yellow, otherwise green. I cannot get the function to change health to green when the due date is more than 5 days away. Below is my formula. What am I doing wrong? Any advice would be great :)
=IF(Due@row < TODAY(), "Red", IF(AND(TODAY() - Due@row <= "5d", [% Complete]@row = 0), "Red", IF(AND(TODAY() - Due@row <= "5d", [% Complete]@row <= 0.5), "Yellow", IF(TODAY() - Due@row > "5d", "Green"))))
Thank you.
Best Answers
-
Hi Mark, I understand your struggles having used excel since version 1. The link below will become one of you best Smartsheet friends.
https://help.smartsheet.com/functions
The formula as is counts all days. You would use WORDAYS if you want to remove weekends. The formula for 5 workdays from today is: WORKDAY(today(), 5)
Try this:
=IF(Due@row < TODAY(), "Red", IF(due@row> today(5), "Green", IF(AND(TODAY() - Due@row <= 5, [% Complete]@row = 0), "Red", IF(AND(TODAY() - Due@row <= 5, [% Complete]@row <= 0.5), "Yellow", "Green"))))
Work now?
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
Excellent. Glad you found a solution. Please accept an answer to close the discussion. Thank you for contributing to the Community.
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
Answers
-
Try:
=IF(Due@row < TODAY(), "Red", IF(AND(TODAY() - Due@row <= 5, [% Complete]@row = 0), "Red", IF(AND(TODAY() - Due@row <= 5, [% Complete]@row <= 0.5), "Yellow", "Green")))
Work?
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
Hi Mark,
Thanks for your help! Unfortunately, it did not work. But, I will say that all of it worked until you get to the due date that is over 5 days out. For due dates over 5 days out, it still comes back with yellow in the results if the % Complete is less than or equal to 50% and red for lines where the due date is more than 5 days out and % Complete is zero. I want it to make anything with a due date more than 5 days out, green.
A couple of questions. One, it seems that you can just put in 5 for 5 days when taking the difference between today and the due date? It also seems when counting the days out for due date, it only counts weekdays?
Thanks for your help! I am used to formulas in Excel and have had a ton of trouble with this.
-Mark Singleton
-
Hi Mark, I understand your struggles having used excel since version 1. The link below will become one of you best Smartsheet friends.
https://help.smartsheet.com/functions
The formula as is counts all days. You would use WORDAYS if you want to remove weekends. The formula for 5 workdays from today is: WORKDAY(today(), 5)
Try this:
=IF(Due@row < TODAY(), "Red", IF(due@row> today(5), "Green", IF(AND(TODAY() - Due@row <= 5, [% Complete]@row = 0), "Red", IF(AND(TODAY() - Due@row <= 5, [% Complete]@row <= 0.5), "Yellow", "Green"))))
Work now?
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
Hi Mark, yes, that worked! Thank you.
Obviously, reversing the order of the If statement to have the "Green" clause be second and changing the red and yellow to a nested if made a difference. Thank you again!
I bookmarked the functions page. I know that will be helpful, too. Thank you!
-Mark
-
Excellent. Glad you found a solution. Please accept an answer to close the discussion. Thank you for contributing to the Community.
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!