Conditional Formatting using 2 conditions

RRico

Board Regular
Joined
Nov 3, 2004
Messages
98
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.

1686918284896.png







Know how to resolve this? 😳 :)
 

Attachments

  • 1686918267887.png
    1686918267887.png
    9 KB · Views: 6

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Are you saying that you get a yellow fill when the date is less than today?
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
How about
Excel Formula:
=isoweeknum(I2)=isoweeknum(today())
 
Upvote 0
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.
 
Upvote 0
It works for me.
Fluff.xlsm
I
1
213/06/2023
319/06/2023
418/06/2023
511/06/2023
612/06/2023
7
Lists
Cells with Conditional Formatting
CellConditionCell FormatStop If True
I2:I11Expression=ISOWEEKNUM(I2)=ISOWEEKNUM(TODAY())textNO
 
Upvote 0
1686924933828.png



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

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]
1686924466836.png

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]
1686924610501.png

When i use this date of the 19th, which is the 1st day of next week, it's no longer highlighting [expected/good]
1686924803362.png
 
Upvote 0
Do you have any other conditional formatting rules?
Also for the 1st rule did you specify a white fill?
 
Upvote 0
"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 :)
1686926670026.png
 
Upvote 0

Forum statistics

Threads
1,223,909
Messages
6,175,313
Members
452,634
Latest member
cpostell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top