Task Status Formula help
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?
Comments
-
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:andree@workbold.com | 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:andree@workbold.com | 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
Categories
Check out the Formula Handbook template!
