At Risk with % Complete & Dates Formula

lcymr13

Oh formulas. . . I feel like I am close, but could use some help.

I would like the 'risk' flag to be marked if the data meets the following criteria:

% complete is equal to or less than 50% AND

The date is within 3 days; Today; or is in the past

=IF(AND(([% Complete]@row >= ".5"), (Finish@row >= TODAY(+3), Finish@row <= TODAY(), Finish@row = TODAY(-1) )), 1, 0)

@Paul Newcome you seem to know what you are doing. Thought I would tag you (been reading/searching a lot). ;-)

Paul Newcome

Haha. Thanks for the vote of confidence. Lets see what we can do here.

Because we have multiple criteria, we know we need to start with

=IF(AND(

First criteria is that [% Complete] is less than or equal to 50%

=IF(AND([% Complete]@row<= .5,

Next criteria is Finish is within the next 3 days, is today, or is in the past. Lets simplify this a little bit. Today is less than 3 days from now, and so is the past. So all we really need to say is that Finish is less than 3 days from today.

=IF(AND([% Complete]@row<= .5, Finish@row <= TODAY(3)

Since that is the last of our criteria, we can close out the AND which is the logical statement portion of our IF and finish off the rest of the formula.

=IF(AND([% Complete]@row<= .5, Finish@row <= TODAY(3)), 1)

Give that one a whirl and see if it works for you.

Paul Newcome

Haha. Thanks for the vote of confidence. Lets see what we can do here.

Because we have multiple criteria, we know we need to start with

=IF(AND(

First criteria is that [% Complete] is less than or equal to 50%

=IF(AND([% Complete]@row<= .5,

Next criteria is Finish is within the next 3 days, is today, or is in the past. Lets simplify this a little bit. Today is less than 3 days from now, and so is the past. So all we really need to say is that Finish is less than 3 days from today.

=IF(AND([% Complete]@row<= .5, Finish@row <= TODAY(3)

Since that is the last of our criteria, we can close out the AND which is the logical statement portion of our IF and finish off the rest of the formula.

=IF(AND([% Complete]@row<= .5, Finish@row <= TODAY(3)), 1)

Give that one a whirl and see if it works for you.

lcymr13

@Paul Newcome that worked. Thank you so very much!

Paul Newcome

Happy to help! 👍️