Using IF(OR(ISDATE function

Todd Lozo

Hello,

I'm trying to compare the date in the white column against the dates in the 3 blue columns (below). If there is a date listed in at least 1 of the 3 blue columns, and one of those date is less than or equal to the date in the white column, then it will return a "Yes". If not, it returns "No".

The dates listed in the white column never change and there will always be a date listed, whereas the blue columns may or may not have a date listed.

I was able to create an IF(OR formula that worked well if a date was listed in the blue columns, but it returns an #INVALIDOPERATION error if there's anything other than dates listed in the blue columns (the "not reported" text above).

Here's the original formula I created:

=IF(OR([CMS Power Derate Date]@row >= [Warranty End Date + 2 Years]@row, [CMS Thrust Derate]@row >= [Warranty End Date + 2 Years]@row, ISDATE([CMS Shutdown Date]@row >= [Warranty End Date + 2 Years]@row)), "Yes", "No")

I then tried to update the formula so it would only compare dates if there is a date listed in the blue columns, but I still get the same error. Here's the updated formula:

=IF(OR(ISDATE([CMS Power Derate Date]@row >= [Warranty End Date + 2 Years]@row), ISDATE([CMS Thrust Derate]@row >= [Warranty End Date + 2 Years]@row), ISDATE([CMS Shutdown Date]@row >= [Warranty End Date + 2 Years]@row)), "Yes", "No")

Any ideas would be greatly appreciated. Thanks!

Paul Newcome

Try this:

=IF(IFERROR(MIN(COLLECT([1st Date]@row:[3rd Date]@row, [1st Date]@row:[3rd Date]@row, ISDATE(@cell))), [Warranty End Date + 2 Years]@row + 1)< [Warranty End Date + 2 Years]@row, "Yes", "No")

Paul Newcome

Try this:

=IF(IFERROR(MIN(COLLECT([1st Date]@row:[3rd Date]@row, [1st Date]@row:[3rd Date]@row, ISDATE(@cell))), [Warranty End Date + 2 Years]@row + 1)< [Warranty End Date + 2 Years]@row, "Yes", "No")

Victoria_Indimar

Hello! If I'm understanding that you're trying to do, I think the formula might be a bit simpler.

I interpreted it as, for each date in the white column, compare it against the dates in the same row in each of the blue columns. If any of them are less than or equal, output YES, if not OR if there are no dates, output NO.

I used the Join and Collect functions.

=IF(JOIN(COLLECT([blue1]@row:[blue3]@row, [blue1]@row:[blue3]@row, <= white@row)) = "", "No", "Yes")

The Collect does the comparison - collect any values in the blue that is less than or equal to the white. If there's not a date or if it's greater, there is no value to collect. Collect has to be used with another function, so I used Join so if there are multiple dates that meet that criteria, it'll just list all the dates that were collected.

If any dates were collected, that means there must be a criteria match. That's where I used the If function. It checks if the Join-Collect has any values. If it doesn't (="") then it's a No, if it does, it's a Yes.

Hope this works for you and is what you were looking for. Please let us know. Thanks!

Todd Lozo

@Paul Newcome's solution worked - thanks for the responses!

Paul Newcome

Happy to help. 👍️