At Risk formula include when Status is <blank>

Getting started in Smartsheets for Project Management. I've been reading a number of Q/A on creating formulas to flag At Risk. I want to flag any task 3 days before the End Date or if it's past the End Date. Additionally, flagging any task where the Status is NOT Complete.

Here is what I've been using so far:

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

However, it's not flagging any task where the Status hasn't been updated. In other words, the status is <blank>. How do I adjust the formula to pick up this incomplete status?

My status options are Complete, Not Started, In Progress, Blocked or <blank>.

Find more posts tagged with

Formulas

Project Management

Accepted answers

Sing C

Hi @Patrick360,

If I understand the ask correctly, give the following a try:

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

A few notes:

I came across ISBLANK last year and find it gives me what I need where I had previously been using =""
The change from TODAY(-3) to TODAY(3) should correctly evaluate any task where the end date is within 3 days of the current date. For example, taking today's date (06-Dec), any task where End Date is 07-Dec, 08-Dec, 09-Dec should show as At Risk.
Changing the NOT clause to use status <> instead should capture blank statuses.

I hope this helps. Let me know if that works for you!

Have a great day.

All comments

Sing C

Hi @Patrick360,

If I understand the ask correctly, give the following a try:

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

A few notes:

I came across ISBLANK last year and find it gives me what I need where I had previously been using =""
The change from TODAY(-3) to TODAY(3) should correctly evaluate any task where the end date is within 3 days of the current date. For example, taking today's date (06-Dec), any task where End Date is 07-Dec, 08-Dec, 09-Dec should show as At Risk.
Changing the NOT clause to use status <> instead should capture blank statuses.

I hope this helps. Let me know if that works for you!

Have a great day.

Patrick360

Sing,

Very helpful. I also appreciate the notes you included as it helped me learn instead of just copying a formula. Thanks for your consult.

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.