Combining Multiple IF(AND) Formulas.

Options

I have studied the Smartsheet Community boards but am unable to get my formula to work. My goal would be to combine the following rules into 1 formula:

1. If the "% Complete" column is 100% then leave the "At Risk" flag unmarked.
2. Mark the "At Risk" flag if the "Start" date was passed and the "% Complete" is less than .1 (10%)
3. Mark the "At Risk" flag IF the "Finish" date has passed and the "% Complete" is less than 1 (100%)

Please see attached screen shot showing my columns and Formula

• ✭✭✭✭✭✭
edited 02/23/21 Answer ✓
Options

Hi @Kendrick Hanny

Hope you are fine, please try the following formula and convert it to column formula:

=IF(AND(Start@row < TODAY(), [% Complete]@row < 0.1), 1, IF(AND(Finish@row < TODAY(), [% Complete]@row < 1), 1, 0))

screenshot show the result

bassam.khalil2009@gmail.com

☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

• ✭✭✭✭✭✭
edited 02/24/21 Answer ✓
Options

You are welcome

Thank you for accepting my answer 🤗

bassam.khalil2009@gmail.com

☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

• ✭✭✭✭✭✭
edited 02/23/21 Answer ✓
Options

Hi @Kendrick Hanny

Hope you are fine, please try the following formula and convert it to column formula:

=IF(AND(Start@row < TODAY(), [% Complete]@row < 0.1), 1, IF(AND(Finish@row < TODAY(), [% Complete]@row < 1), 1, 0))

screenshot show the result

bassam.khalil2009@gmail.com

☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

• Options

Wow! Thank you! Worked great.

• ✭✭✭✭✭✭
edited 02/24/21 Answer ✓
Options

You are welcome

Thank you for accepting my answer 🤗

bassam.khalil2009@gmail.com

☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!