Red Flag for Past Due

Options

Hi!

Hoping someone can help. I'd like a red flag in a column for a task that is overdue reading a status column. When status is complete, it no longer needs to flag. I can't get this to work! At the moment I'm using:

=IF([Due Date Q1]@row = "", 0, IF(OR([% Complete]@row < 1, [Due Date Q1]@row < TODAY()), 1, 0))

I need the %Compelte column ref taken out but I can't get this to work.

At the moment, it's flagging past due but not stopping the flag if it's marked as complete.

Thanks!

Answers

  • DanniB
    Options

    =IF(OR(AND([Due Date Q1]@row < TODAY(), [Owner Status]@row = "Not Started"), AND([Due Date Q1]@row < TODAY(), [Owner Status]@row = "In Progress"), IF(AND([Due Date Q1]@row < TODAY(), [Owner Status]@row = "Comments Provided"), 1, 0)))


    Had a go.....it was working great until I added that I needed a flag for the Owner Status column to read 'Comments Provided' as a flag too....any ideas?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    edited 04/11/22
    Options

    Try this:

    =IF(AND([Due Date Q1]@row< TODAY(), OR([Owner Status]@row = "Not Started", [Owner Status]@row = "In Progress", [Owner Status]@row = "Comments Provided")), 1)

  • Emily Zeiger
    Emily Zeiger ✭✭✭✭
    Options

    You didn't finish the first IF statement and you were missing a ) right before that. Alternatively, if they all result in the same flag if true, you could get rid of the second IF - see the second formula. Hopefully one of these helps.


    =IF(OR(AND([Due Date Q1]@row < TODAY(), [Owner Status]@row = "Not Started"), AND([Due Date Q1]@row < TODAY(), [Owner Status]@row = "In Progress")), INSERT WHAT HAPPENS IF TRUE HERE, IF(AND([Due Date Q1]@row < TODAY(), [Owner Status]@row = "Comments Provided"), 1, 0)))


    =IF(OR(AND([Due Date Q1]@row < TODAY(), [Owner Status]@row = "Not Started"), AND([Due Date Q1]@row < TODAY(), [Owner Status]@row = "In Progress"), AND([Due Date Q1]@row < TODAY(), [Owner Status]@row = "Comments Provided")), 1, 0)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!