nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Smartsheet Community

Connect with peers, share your expertise, and inspire what’s next in Smartsheet — from proven practices to practical how-to insights from fellow users and product experts.

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). ;-)

Find more posts tagged with

Accepted answers

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.

All comments

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,

=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! 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.