Return "Yes" or "No" in IF statement with multiple criteria - check if date is past due

Options

This one is proving difficult for me - trying to create a helper column that does the following

'IF (deadline cell) isn't BLANK, AND (status cell) is one of "Active" or "Closed", AND (deadline cell) IS in the past return 'Yes" (past due), otherwise "No"

Would love there to be a way of doing this as a summary sheet field (i.e count how many rows have deadlines that are in the past) but that may be a bridge too far!!

Any help gratefully received :)

Best Answer

  • Snaillybob
    Snaillybob ✭✭
    Answer ✓
    Options

    Finally sorted it with this formula

    =IF(Deadline@row <> "", IF(Status@row <> "", IF(Status@row <> "Closed", IF(AND(Deadline@row < TODAY(), Status@row <> "Closed"), "Yes", "No"))))

    Decided that reporting if closed statuses were closed after their deadline passed was unnecessary

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!