How do you set a flag status if a date is in the past and status does not equal a certain criteria?

Options

Hello! I’m having trouble getting a flag to turn on if my Production Wraps column date is in the past and the status is NOT “wrapped” or “wrapped/aired”. Here’s the formula I’m trying to use but am getting #incorrect. Any suggestions?

=IF(AND([Production Wraps]@row > TODAY(), OR(Status@row <> "Wrapped", Status@row <> "Wrapped/Aired")), 1, 0)

Tags:

Best Answer

  • DKazatsky2
    DKazatsky2 ✭✭✭✭✭
    Answer ✓
    Options

    Hi @Will C ,

    I'm not sure why you are getting #incorrect because your formula seems syntactically correct. It could be that you have a non-date in a date field?

    Having said that, if I'm reading your requirement correctly, that formula would not give the desired result. Try this and see if it's the results you are looking for.

    =IF(AND([Production Wraps]@row < TODAY(), Status@row <> "Wrapped", Status@row <> "Wrapped/Aired"), 1, 0)

    Hope that helps,

    Dave

Answers

  • DKazatsky2
    DKazatsky2 ✭✭✭✭✭
    Answer ✓
    Options

    Hi @Will C ,

    I'm not sure why you are getting #incorrect because your formula seems syntactically correct. It could be that you have a non-date in a date field?

    Having said that, if I'm reading your requirement correctly, that formula would not give the desired result. Try this and see if it's the results you are looking for.

    =IF(AND([Production Wraps]@row < TODAY(), Status@row <> "Wrapped", Status@row <> "Wrapped/Aired"), 1, 0)

    Hope that helps,

    Dave

  • Will C
    Will C ✭✭
    Options

    @DKazatsky2 YES! Thank you so much!!!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!