Help with Nested If Function

Mark Singleton

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.

Mark Cronk

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

Mark Cronk

Excellent. Glad you found a solution. Please accept an answer to close the discussion. Thank you for contributing to the Community.

Mark

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

Mark Singleton

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

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

Mark Singleton

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

Excellent. Glad you found a solution. Please accept an answer to close the discussion. Thank you for contributing to the Community.

Mark