Automate At Risk flag

I am very new to Smartsheet - trying to figure this out and could use some help.

I need to have my At Risk flag marked automatically if a task is due [Complete By] within three days and the [% Complete] is not 100%.

The one I am currently using is not working: =IF(AND([Complete By]@row >= TODAY(2), [% Complete]@row < 1), 1, 0)

I know this has been asked before - I have been reading them all (that's where I got that formula) and can't seem to find one that will work.

Find more posts tagged with

Accepted answers

Paul Newcome

You are actually very very close. One thing I do is think of dates as numbers where TODAY() is zero. So tomorrow would be TODAY(1) and yesterday would be TODAY(-1).

Using that logic, we can update your formula with a very minor tweak like so (bold):

=IF(AND([Complete By]@row >= TODAY(-3), [% Complete]@row < 1), 1, 0)

All comments

Paul Newcome

You are actually very very close. One thing I do is think of dates as numbers where TODAY() is zero. So tomorrow would be TODAY(1) and yesterday would be TODAY(-1).

Using that logic, we can update your formula with a very minor tweak like so (bold):

=IF(AND([Complete By]@row >= TODAY(-3), [% Complete]@row < 1), 1, 0)

Cara Sikes

Welp....I feel dumb.

Thanks for the clarification - that worked like a charm and now I understand it better.

Paul Newcome

No need to feel "dumb". You gotta learn somehow, and I am happy to help. 👍️

Tina D

Where do you put this formula? Into each cell that contains a flag? I'm trying to do the same thing. Help!

Paul Newcome

@Tina D Yes. It would go in the flag column.

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.