Less Than Date Comparisons with Blank Cells

Elliot Holder
Elliot Holder ✭✭
edited 12/09/19 in Formulas and Functions

I have a column "Deliverable Past Due" that should return a Yes if any dates in the stated columns are less than today. The issue arises when many of the date columns are blank. The formula recognizes blank cells as being in the past and always returns a "Yes". I tried adding ISDATE to the statement, but then it always returns "No" even when a date is in the past. I pasted the formula below.

=IF(ISDATE(OR([Comp1 Art Due Date]@row < TODAY(), [Comp2 Art Due Date]@row < TODAY(), [Comp3 Art Due Date]@row < TODAY(), [Comp4 Art Due Date]@row < TODAY(), [Comp5 Art Due Date]@row < TODAY(), [Comp6 Art Due Date]@row < TODAY(), [Comp7 Art Due Date]@row < TODAY(), [Comp8 Art Due Date]@row < TODAY(), [Comp9 Art Due Date]@row < TODAY(), [Comp10 Art Due Date]@row < TODAY(), [White Papers Due]@row < TODAY(), [Deck Due Date]@row < TODAY(), [Rendering Due Date]@row < TODAY(), [Client Quote Due]@row < TODAY())), "Yes", "No")

Comments

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!