edited 12/09/19

I am trying to update the below status formula to update the "yellow" status to be determined by a % complete less than 25% and the Due Date (week of) is between 7 and 14 days in the future.

Here is the current formula:

=IF([% Complete]26 = 1, "Green", IF(AND([% Complete]26 <= 0.25, [Due Date (week of)]26 > TODAY()), "Yellow", IF(AND([% Complete]26 >= 0.26, [% Complete]26 <= 0.99, [Due Date (week of)]26 > TODAY()), "Blue", IF(AND([Due Date (week of)]26 <= TODAY(), [% Complete]26 < 1), "Red", ""))))

Here is a formula I have been working with to satifsfy second part of the "yellow" status:

=IF(AND(([Due Date (week of)]32 - TODAY()) > 7, ([Due Date (week of)]32 - TODAY()) < 14), "Yellow")

How do I integrate the second formula into the first one and get it work?

• ✭✭✭✭✭✭

Hi Marie,

Try this.

=IF([% Complete]@row = 1; "Green"; IF(AND([% Complete]@row <= 0,25; [Due Date (week of)]@row >= TODAY(+7); [Due Date (week of)]@row < TODAY(+14)); "Yellow"; IF(AND([% Complete]@row >= 0,26; [% Complete]@row <= 0,99; [Due Date (week of)]@row > TODAY()); "Blue"; IF(AND([Due Date (week of)]@row <= TODAY(); [% Complete]@row < 1); "Red"; ""))))

Same version but with the below changes for your and others convenience.

=IF([% Complete]@row = 1, "Green", IF(AND([% Complete]@row <= 0.25, [Due Date (week of)]@row >= TODAY(+7), [Due Date (week of)]@row < TODAY(+14)), "Yellow", IF(AND([% Complete]@row >= 0.26, [% Complete]@row <= 0.99, [Due Date (week of)]@row > TODAY()), "Blue", IF(AND([Due Date (week of)]@row <= TODAY(), [% Complete]@row < 1), "Red", ""))))

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

I hope this helps you!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭✭✭

Same version but with the below changes for your and others convenience.

World peace at last!!!

• ✭✭✭✭✭✭

I couldn't fight it anymore  (at least not on long and complicated formulas)

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!