Formulas Including Today as Calculation with Blank Values

Taci Shinn

Hello. I'm trying to use the Today function to help calculate a check column. Essentially, I want to compare a Due Date Column and a Completed Date column to return a value in the Two Weeks column.

If Due Date is within the next two weeks of Today AND the Completed column is blank, it should return a value of "1" in the Two Weeks column.

If the Due Date is blank, not within the next two weeks, or if the Completed box isn't empty, it should return a value of "0" in the Two Weeks column.

I'm using the following formula and it works with the exception that it returns a value of "1" in the Two Weeks column even if the Due Date is blank. Any thoughts on how to correct?

=IF(AND([Due Date]@row <= TODAY(+14), Completed@row = ""), "1", "0")

Mark Cronk

Hi @Taci Shinn ,

Try:

=IF(AND(ISDATE([due date]@row), [Due Date]@row <= TODAY(+14), Completed@row = ""), "1", "0")

Work?

Mark

Mark Cronk

Hi @Taci Shinn ,

Try:

=IF(AND(ISDATE([due date]@row), [Due Date]@row <= TODAY(+14), Completed@row = ""), "1", "0")

Work?

Mark

Taci Shinn

Yes! That worked like a charm. Many thanks!

Mark Cronk

Glad you found a solution. Thank you for contributing to the Community.

Mark