Hi, im trying to automate flagged risks within projects I am managing however am struggling

I have made many attempts however it confuses me. I originally wanted to have the formula to flag any tasks that were due within the week

or to flag tasks that will not be accomplished by their due date (this would be ideal however I am not sure my sheet would be equipped for this as my duration is for each task is automatically calculated.

So far I have created this =IF([End Date]1 < TODAY(+7), 1). This was to test and try a basic formula however this flags the task however even if it is okay. Any help would be greatly appreciated. I've attached a screenshot.

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

Are you able to outline the desired logic/function in detail?

chrisfinlay

Hi Paul.

Thanks for the response. I would like the sheet/reports to Flag the task at risk (using the icon) IF the End Date is 1 week away and % Complete is below 50%?

Does that make sense? Im trying to make something as close as possible.

Thanks!

Paul Newcome

Try this:

=IF(AND([End Date]@row<= TODAY(7), [% Complete]@row< 0.50), 1)

chrisfinlay

Hi Paul ,

Thank you! it does works perfectly, I just need to try and add in logic/ a rule to not flag the task if it is complete.

chrisfinlay

I think I got it, =IF([% Complete]@row = 1, 0, IF(AND([End Date]@row <= TODAY(7), [% Complete]@row < 0.5), 1)). It seems to work, does this look correct?

Paul Newcome

My formula shouldn't have been flagging rows that are more than 50% regardless of the [End Date], so it shouldn't have been flagging any rows that were complete or 100%.

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.