How do you write a formula to auto-fill in the flag checkbox in a column with IF/AND:

Options

Here is what I have so far but I am receiving an UNPARSEABLE message:

=IF(AND(Status@row <> Complete, [Planned End Date]@row = TODAY(10), "1", "0"))


Thank you!

Best Answers

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭
    Answer ✓
    Options

    This will only check the box if the end date is exactly 10 days from today. Is that the way you want it to function?

    =IF(AND(Status@row <> "Complete", [Planned End Date]@row = TODAY(10)), 1, 0)

  • Stephanie Pollard
    Answer ✓
    Options

    Thank you, Carson for the quick response! I realize I need to adjust the formula since I was intending on the flag being marked if a due date was approaching but the status was not started or in progress. I changed it to this: =IF(AND(Status@row <> "Complete", [Planned End Date]@row < TODAY(10)), 1, 0) and it seems to have worked!

    Thank you again!

Answers

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭
    Answer ✓
    Options

    This will only check the box if the end date is exactly 10 days from today. Is that the way you want it to function?

    =IF(AND(Status@row <> "Complete", [Planned End Date]@row = TODAY(10)), 1, 0)

  • Stephanie Pollard
    Answer ✓
    Options

    Thank you, Carson for the quick response! I realize I need to adjust the formula since I was intending on the flag being marked if a due date was approaching but the status was not started or in progress. I changed it to this: =IF(AND(Status@row <> "Complete", [Planned End Date]@row < TODAY(10)), 1, 0) and it seems to have worked!

    Thank you again!

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭
    Options

    I suspected that may be what you were going for. I'm glad it worked!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!