Text in Date Column Messing Up Formula

SYSPK

I have 8 date columns where sometimes I enter text. Either the date of a document's expiration, or if the document was simply "received."

I have another helper column that I want to be checked off if any of the 8 dates are expired. BUT I'm finding if I have text in any one of them, the formula becomes "invalid operation."

 

I've already tried these conditions, but I still get "invalid" if a date column has text, even if one of the other columns is expired:

=IF(AND(ISDATE([date column]),[date column] < TODAY()),1

=IF(AND(NOT(ISTEXT([date column])),[date column] < TODAY()),1

=IF(AND(NOT([date column]="received"),[date column] < TODAY()),1

 

The truth is if I had only 1 date column, I would do something like:

=IF(ISTEXT([date column]),"", IF([date column] < TODAY(), 1

But I have 8 to contend with. And I'm doing a nested OR function within the IF because I only need the checkbox to check off if [date column1] is expired or [date column2] is expired or etc.....

 

Thanks for any and all help.

AverageUser

When you are checking the columns that may or may not be a date value, use an IF() to set the value to a date in the formula if it is not a date.

"=IF(AND(IF(ISDATE([DateColumn]1), [DateColumn]1, TODAY()) < TODAY()), 1)"

So if the value is a Date, it will evaluate the date... but if it is anything other than a date, it will evaluate it as if it is today's date when executing the formula.

Basically, you are tricking the system to think it is a date, even if it isn't a date.

I don't even think the "AND" is coming into play in your examples since there is only 1 value in the resulting logic.

You could avoid the "AND" altogether by evaluating each date column as its own PASS/FAIL and if any of them PASS (date is older than today), then check your "EXPIRED" box.

Here is an example of how you might use it with multiple date columns being evaluated and not using "AND":

"=IF(IF(ISDATE([DateColumn1]1), [DateColumn1]1, TODAY()) < TODAY(),1,

 IF(IF(ISDATE([DateColumn2]1), [DateColumn2]1, TODAY()) < TODAY(),1,

 IF(IF(ISDATE([DateColumn3]1), [DateColumn3]1, TODAY()) < TODAY(),1,

 IF(IF(ISDATE([DateColumn4]1), [DateColumn4]1, TODAY()) < TODAY(),1,0))))"

Paul Newcome

Try something like this... We will pull the earliest date using the MIN function, and we will tell that to only look at dates using the COLLECT function. Then we say that if the earliest date is expired then check the box.

 

=IF(MIN(COLLECT([First Date Column]@row:[Last Date Column]@row, [First Date Column]@row:[Last Date Column]@row, ISDATE(@cell))) < TODAY(), 1)

AverageUser

Well... you just had to go and make a super elegant solution that makes mine obsolete  lol.

I love this approach and had never used the COLLECT function. Definitely going to familiarize myself with it now.

However, it would still produce an error if ALL of the referenced date fields in a row had text values (but I bet that would never happen). 

Paul Newcome

I was just trying to show a second solution is all. Lol. 

 

If there are no dates (either all fields blank or all fields text), it will return a blank (no error). If there is one date within the range, it will hit on that date. If there are two or more dates, it will compare the earliest date to today's date.

Paul Newcome

The COLLECT function actually provides a HUGE amount of flexibility in many different applications. I absolutely love it.

 

We have a SUMIFS and a COUNTIFS, but no AVGIFS. 

 

=AVG(COLLECT(.........................)) 

 

will give you that option.

 

Have a range that you want to use the JOIN function on, but hate seeing the extra delimiters in place of the blank fields?

 

=JOIN(COLLECT(Range, Range, ISTEXT(@cell), delimiter)

 

I know I use the COLLECT function a lot more than that, but those are the first two that I could think of.

SYSPK

YES IT WORKED!!

Thank you. This is gold.

 

In case this helps anyone else:

One of my date columns happens to be a parent. So I added that as an IF condition first:

=IF(PARENT([date column]1)<TODAY(),1,IF(the min/collect formula above for the rest of the columns @row.......)

Paul Newcome

Happy to help! Glad you were able to get things working.