Checkbox Formula: If Due Date has passed but completion date is blank

I have two columns:

1: A "Date Calc" field (contains a formula) that automatically displays a due date for a task based on manually entered dates from a prior field. This is a date column.

2: A Date Complete field that is manually entered. This is a date column.

I have added a third column that will automatically check a box if the Date Calc field is:

  1. In the past
  2. The Date Complete is blank

I do not want the box to be checked if the Date Complete field is NOT blank.

I have used the following formula which is almost there, but it is triggering a checkbox for dates in the Date Calc field that are NOT in the past.

This is what I am using: =IF([Date Calc]@row < TODAY(), ISBLANK([Date Complete]@row), 1)


Best Answers

  • Summer
    Summer ✭✭✭
    edited 02/23/22 Answer ✓

    Hi @Paula Meunier,

    Consider updating your formula to an IF(AND and also adding in the operation to tell the formula what to do if the conditions are not met.

    In my testing the formula in the checkbox column looked like this:

    =IF(AND([Date Calc]@row < TODAY(), ISBLANK([Date Complete]@row)), 1, 0)


    Logical Expression 1: Date Calc row less than today

    Logical Expression 2: Date Complete cell is blank

    )) to close out the IF And

    , to switch to value if true

    True = 1 (Check the box)

    , to switch to value if false

    false = 0 (don't check the box)

  • Paula Meunier
    Paula Meunier ✭✭✭✭
    Answer ✓

    It works! Thank you!


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!