Text in Date Column Messing Up Formula

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.

Find more posts tagged with

Formulas

Comments

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Circular Reference with formula
i am not good with writing formulas within a sheet. I asked the AI tool in the sheet to help me write a formula and I am getting a Circular reference back. I'm trying to have it write the following formula for me. If the column "Tracking #" is not blank, change Column "Deployment Phase" to "Shipped" I have tried adding in…
How to delay the execution of a workflow whose trigger occurred much earlier?
I have a workflow that detects a row(date) that is changing its Fiscal Week Finish Date and send this row to other Sheet. Long story short: I collect delayed tasks in separate file & I tag reasons for delay for these tasks. It happens that plan are analyzed 2-3 hours and dates are being changed several times. I would like…
INDEX/COLLECT??
Having trouble with an INDEX/COLLECT/CONTAINS formula. I have two sheets, my source and my destination; the source sheet has a column I want to collect into the destination. -Tool ID, is in both sheets and the target of the INDEX/MATCH, the source sheet might have this listed 6 or 7 times -Purpose of Device, there are…