Conditional formatting rule for any text string being present

[wrecclesham]

I use the following conditional formatting rule to highlight any dates older than 120 days:

=TODAY()-D2:D11>=120

<a href="https://imgur.com/FaPdt6P"><img src="https://i.imgur.com/FaPdt6P.png" title="source: imgur.com" /></a>

How can I modify this formula to have an additional, alternative criteria of any text string being present?

 

[wrecclesham]

Re: =OR function always returns "FALSE"

I tried using the following formula but it continually returns a "FALSE" result, for either of the two conditions.

=OR(TODAY()-D2:D11>=120,ISTEXT(D2:D11))

However, if I try using the same two conditions individually, they work as expected and the conditional formatting rule is applied.

=TODAY()-D2:D11>=120

=ISTEXT(D2:D11)

Any ideas what I'm doing wrong when I try to combine these two conditions using the OR function?

 

[DanteAmor]

Re: =OR function always returns "FALSE"

Try this

=OR(IFERROR(TODAY()-D2,0)>=120,ISTEXT(D2))

On Applies to:

=$D$2:$D$11

 

[Peter_SSs]

Re: =OR function always returns "FALSE"

Does this do what you want?

Excel Workbook
	D
1	Data
2	5/07/2019
3	6/09/2017
4
5	adsas
6	23/06/2019
7	23/09/2010
8	28/7/19 text
9	28/09/2020
CF Old Date or Text

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2	1. / Formula is =TODAY()-N(D2)>=120	Abc

 

[wrecclesham]

Re: =OR function always returns "FALSE"

The IFERROR function works great.

There was a mistake in my previous comment where I said that there was only a problem in the entire OR function.

As I think you already noticed, a "#VALUE!" error (instead of a "FALSE") was returned when a text string was detected by the date logic, and that error simply gave the entire OR function a "#VALUE!" error also. The IFERROR function solves this.

This is interesting. I guess it means that an error is treated as logically identical to a "FALSE" in conditional formatting.

Thanks!

 

[DanteAmor]

Re: =OR function always returns "FALSE"

The IFERROR function works great.

There was a mistake in my previous comment where I said that there was only a problem in the entire OR function.

As I think you already noticed, a "#VALUE!" error (instead of a "FALSE") was returned when a text string was detected by the date logic, and that error simply gave the entire OR function a "#VALUE!" error also. The IFERROR function solves this.

This is interesting. I guess it means that an error is treated as logically identical to a "FALSE" in conditional formatting.

Thanks!

As the result is error then it is not TRUE.

I'm glad to help you. Thanks for the feedback.

 

[Peter_SSs]

Re: =OR function always returns "FALSE"

Doesn't the simple CF formula in post 4 do the same job?