Conditional Formatting using 2 conditions

[RRico]

Hi everyone, have a Conditional Formatting issue when using 2 conditions below:

Condition #1: Red font when cell w/ red font is not today (past-due) [this works perfectly by itself]
=AND(I2<TODAY(),NOT(ISNUMBER(L2)))

Condition #2: Highlight background of cell when that same cell's date is today [this works perfectly by itself]
=$I2=TODAY()

Symptom: when I combine the two as depicted below... the Condition #2 cancels out/overrides Condition #1.

Know how to resolve this?

 

[Fluff]

Are you saying that you get a yellow fill when the date is less than today?

 

[RRico]

Update... i may delete this thread... i'm actually noticing that i may not have an issue after-all.
I will test some more now.

 

[RRico]

OK, so the issue I'm seeing is that Condition #2 is not working correctly. The goal is for Condition #2 to highlight that cell (i9) when that cell's date is within the same week that we're currently residing within. If i can get that to work, i think both will the override symptom will be totally acceptable after-all.

I think i'm using the wrong formula for Condition #2?

 

[Fluff]

How about

=isoweeknum(I2)=isoweeknum(today())

 

[RRico]

Interestingly... that didn't change a thing. It's only highlighting because today's the 16th and the cell has that 16th as the date, but when I change the date to anything less than today... it will loose the hightlight (even using dates within the same week). If i change it to a date 2 weeks from now, it's still highlighting it as if the date is in the same week.

 

[Fluff]

It works for me.

Fluff.xlsm
	I
1
2	13/06/2023
3	19/06/2023
4	18/06/2023
5	11/06/2023
6	12/06/2023
7
Lists

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I2:I11	Expression	=ISOWEEKNUM(I2)=ISOWEEKNUM(TODAY())	text	NO

 

[RRico]

If i use this date, it goes red, because it's older than today's date (missed target date) [expected good result].

If i use the date of the 13th, it's still Red, which is OK because it's older than today, but ... because it's within the week of today (16th), it's not highlighting [bad result]

If i use today's date of the 16th OR any date between 16th thru 18th, it then highlights yellow [expected/good because we're presently within that week]

When i use this date of the 19th, which is the 1st day of next week, it's no longer highlighting [expected/good]

 

[Fluff]

Do you have any other conditional formatting rules?
Also for the 1st rule did you specify a white fill?

 

[RRico]

"No Color" is specified as the "Fill" for the 1st Rule.
Not other Rules other than those 2 Conditional formats with the exception of these below, but they're ALL for Column M only