Risk flag when past due date and task not started / no due date set

Hello!

I'm very new to formulas, I would love if someone could please point me in the right direction - I'm trying to get the flag to turn red when a planned finish date is in the past AND the status is either in progress or not started. But I don't want it to flag if it's not started and there isn't a planned finish date yet. Is that possible? The formula I'm trying is:

=IF(AND([Planned Finish Date]@row <= TODAY(), AND(Status@row = "Not Started", Status@row = "In Progress")), 1, 0)

But it's not quite right.

Thanks!

B

Answers

  • JamesB
    JamesB ✭✭✭✭✭✭

    @Smartsheet4B

    The AND formula is encompassing, you do not need to put and more than once, just have all your arguments within 1 set of parentheses separated by commas. Also because you are looking for 2 different criteria in your Status row you need an OR argument as a part of the AND statement.

    =IF(AND([Planned Finish Date]@row <= TODAY(),OR(Status@row = "Not Started", Status@row = "In Progress")), 1, 0)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!