Less Than Date Comparisons with Blank Cells

Elliot Holder

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")

Mike Wilday

What was your original formula?

Andrée Starå

Hi Davey,

I'd recommend using a combination of IF AND and maybe OR to check if it's a date and if it's in the past.

Would that work?

I hope that helps!

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

 

Paul Newcome

I would suggest a slightly different approach. COLLECT all of the dates across a range consisting of those columns. Pull the earliest date. If that is less than today, flag "Yes", otherwise flag "No".

 

=IF(MIN(COLLECT([Comp1 Art Due Date]@row:[Client Quote Due]@row, [Comp1 Art Due Date]@row:[Client Quote Due]@row, ISDATE(@cell))) < TODAY(), "Yes", "No")

Elliot Holder

Yes, this did the trick. Thank you!

Paul Newcome

Excellent! Happy to help!