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)

Paula Meunier

It works! Thank you!

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)

Paula Meunier

It works! Thank you!

Summer

Yay! I'm very happy to hear that. Have a lovely day.

Decall95

Summer, I have a similar issue - maybe you can help. I am trying to use an "expires on" column to populate a checkbox column - however, many times the expires on column is blank - in those instances, I do not want the box checked in the other column - this is my original formula, and the one I tried that caused the error - any ideas to tweak it to solve the problem?

=IF([Expires on]@row < TODAY(90), 1, 0)

=IF(AND([Expires on]@row < TODAY(90), ISBLANK([Expires on]@row)), 1, 0))

Genevieve P.

Hi @Decall95

I'd suggest adding an IF statement at the front, like so:

=IF([Expires on]@row = "", 0, IF([Expires on]@row < TODAY(90), 1, 0))

Cheers,

Genevieve

Decall95

https://community.smartsheet.com/discussion/comment/375716#Comment_375716

This worked, thank you so much!!