Forumla help sought so cancelled tasks are not counted as at risk

This formula surfaces 'At risk tasks' to a project rollup dashboard report. It does so based on the criteria of due date in the past and status other than complete.

=IF([End Date]@row <> "", IF(AND([End Date]@row < TODAY(), Status@row <> "Complete"), 1, 0))

Question - What needs to be changed so that it does not surface tasks with the status 'cancelled' as these are not at risk.

Find more posts tagged with

Sheets

Formulas

Reports

Dashboards

Accepted answers

All comments

Carson Penticuff

Give this a try.

=IF([End Date]@row <> "", IF(AND([End Date]@row < TODAY(), Status@row <> "Complete", Status@row <> "Cancelled"), 1, 0))

Phil Wightman

Tx @Carson Penticuff

Computer say UNPARSABLE!

Carson Penticuff

I have it working on a test sheet with no issues, so there must be some difference between how your sheet is setup and how I am testing it. How is your Status row formatted?

Phil Wightman

@Carson Penticuff tx again. I had due date and not end date in the sheet I was using.

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.