Late Flag to trigger based on multiple date fields and not blank

Options

I want the Section 2 Late flag to be red if 

Section 2 Due Date is in the past (and the Section 2 Due Date is not blank)

and if Section 2 Approval Date is not blank.

Best Answer

  • leahwoosley
    leahwoosley ✭✭
    Answer ✓
    Options

    I got this to work!


    =IF(AND([Section 2 Due Date]@row < TODAY(), NOT(ISBLANK([Section 2 Due Date]@row))), NOT(AND([Section 2 Approval Date]@row < TODAY(), NOT(ISBLANK([Section 2 Approval Date]@row)))))

Answers

  • Sam M.
    Sam M. ✭✭✭✭✭
    Options

    Hi Leah,

     

    Maybe you can try this inside the checkbox column:

     

    =IF(AND([Section 2 Due Date]@row < TODAY(), NOT(ISBLANK([Section 2 Due Date]@row)), NOT(ISBLANK([Section 2 Approval Date]@row))), 1)

     

    Based on the description:

     

    [Section 2 Due Date]@row < TODAY()

    Section 2 Due Date is in the past 

     

    NOT(ISBLANK([Section 2 Due Date]@row))

    (and the Section 2 Due Date is not blank)

     

    NOT(ISBLANK([Section 2 Approval Date]@row))

    Section 2 Approval Date is not blank

     

    All of that contained in a AND() for the different conditions to be met.

     

    Hope it helps,

    Samantha

  • leahwoosley
    Options

    I've tried similar, and it's flagging the opposite

    The ones with Due Dates in Feb aren't flagging as overdue, and the one that has been complete (section 2 Approval Date populated) is the one that flags (and shouldn't)


  • leahwoosley
    leahwoosley ✭✭
    Answer ✓
    Options

    I got this to work!


    =IF(AND([Section 2 Due Date]@row < TODAY(), NOT(ISBLANK([Section 2 Due Date]@row))), NOT(AND([Section 2 Approval Date]@row < TODAY(), NOT(ISBLANK([Section 2 Approval Date]@row)))))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!