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

Best Answer

  • Sing C
    Sing C ✭✭✭✭✭✭
    Answer ✓

    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 Chen

    Process Architect, Ceridian

    LinkedIn

Answers

  • Sing C
    Sing C ✭✭✭✭✭✭
    Answer ✓

    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 Chen

    Process Architect, Ceridian

    LinkedIn

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!