Date based formulas - ignore empty Date column

Options

Hello everyone,

We often use formulas to check a checkbox type of column based on a "Date" input. Eg, "Mark Overdue" formula:

=IF([Due Date]@row < TODAY(), 1))

The issues is the checkbox is checked even if the Date column is empty. So when there is no Due Date, the task is marked "Overdue".

Is there any way or adjusting the formula so it "ignores" items with no Due Date?

Thank you!

Best Answers

  • Paul McGuinness
    Paul McGuinness ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @Petra Stewart

    This formula should do what you need

    =IF(ISBLANK([Due Date]@row), false, IF([Due Date]@row < TODAY(), true, false))

    Tested as below and working

    Hope that helps

    Thanks

    Paul

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @Petra Stewart ,

    Something along the lines of this should work:

    =IF(ISDATE([Due Date]@row), IF([Due Date]@row < TODAY(), 1), "")

    I would hazard a guess that your sheet probably has some additional columns that you would want to include in any formula to highlight whether it is overdue or not rather than just if the due date has passed but I can't write the formula without knowing what these are.

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!