At Risk formula include when Status is <blank>

Patrick360

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

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.

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.