Task Overdue

Options

Dear Community,

Can someone please help me to correct my formula here . I want to show that if the due date is over and the task status is not marked Completed or Cancelled it should show as a Overdue.


Best Answer

  • Rich Stowell
    Rich Stowell ✭✭✭✭✭
    Answer ✓
    Options

    Aatish,

    Your formula has conflicting statements in the AND() function. You need to insert an OR() function for the Status column range. Try this:

    If (

    and (

    or (

    Contains("Assigned",Status@row),

    Contains("In-Progress",Status@row),

    Contains("Awaiting more Information",Status@row)

    ),

    Today() > [Due Date]@row

    ),

    "Overdue",

    ""

    )

Answers

  • James Keuning
    James Keuning ✭✭✭✭✭
    Options

    I’m on the bus, so I can’t test this, but you need to connect the tests of the status field with an OR statement.

    You could also use NOT “Completed” instead of testing the alternatives.

    bascially you arrive at NOT “completed” AND due date in the past

  • Rich Stowell
    Rich Stowell ✭✭✭✭✭
    Answer ✓
    Options

    Aatish,

    Your formula has conflicting statements in the AND() function. You need to insert an OR() function for the Status column range. Try this:

    If (

    and (

    or (

    Contains("Assigned",Status@row),

    Contains("In-Progress",Status@row),

    Contains("Awaiting more Information",Status@row)

    ),

    Today() > [Due Date]@row

    ),

    "Overdue",

    ""

    )

  • Aatish Chaudhary
    Options

    Thanks both for taking time for helping me on this. It worked and I am all set now :)

  • Aatish Chaudhary
    Options

    Hi Rich,

    Can you also help how I can combine these two formulas together. Currently I am using them in two individual columns and both formulas are working fine. The column Status Override is checkbox column and if I select the checkbox column the overdue status can be removed.

    So basically first we need the show the overdue based on the first formula and if we wanted to remove the overdue status we just need to select the checkbox column "Status Override".

    =IF(AND(OR(CONTAINS("Assigned", Status@row), CONTAINS("In-Progress", Status@row), CONTAINS("Awaiting more Information", Status@row)), TODAY() > [Due Date]@row), "Overdue", "")

    =IF([Status Override]@row = "True", "", (IF(IFERROR(VLOOKUP([Row ID]@row, {BACKUP}, 1, 0), "") <> "", "Overdue")))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!