Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

if/and/or formula help

I have a set of training records data. Some have required due dates, some do not. I need a formula that set s flag (for items needing attention) if:

  1. the complete date is blank
  2. the complete date is > the due date (if there is a due date)
    1. for this condition, i need it to ignore if the due date is blank

When I use this formula, it works, except it does not handle the blank due dates:

=IF(OR([Complete Date]@row = "", [Due Date]@row < TODAY()), 1, 0))

how should i modify this to accommodate 2.a scenario?

Tags:

Best Answer

  • Community Champion
    Answer ✓

    Hi @SRaben2,

    The write-up you have and the formula you show do not line up. In the formula you are checking the Due Date against the current date, this is not mentioned in your write-up.

    Going strictly by what you stated, the following should work.

    =IF(OR([Complete Date]@row = "", AND([Complete Date]@row > [Due Date]@row, NOT(ISBLANK([Due Date]@row)))), 1, 0)

    Hope this helps,

    Dave

Answers

  • Community Champion
    Answer ✓

    Hi @SRaben2,

    The write-up you have and the formula you show do not line up. In the formula you are checking the Due Date against the current date, this is not mentioned in your write-up.

    Going strictly by what you stated, the following should work.

    =IF(OR([Complete Date]@row = "", AND([Complete Date]@row > [Due Date]@row, NOT(ISBLANK([Due Date]@row)))), 1, 0)

    Hope this helps,

    Dave

  • THANK YOU DAVE!!! You saved the day!!!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions