Task Status Formula help

Marieinsd

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?

 

 

 

Andrée Starå

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