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

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓

    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.

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓

    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

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭

    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

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓

    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

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!