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

Paula Meunier

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)

Summer

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)

IF(AND(

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)