# Help with Nested If Function

Options

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.

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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.

• Options

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

• ✭✭✭✭✭✭
Options

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.

• Options

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

• ✭✭✭✭✭✭
Options

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!