Conditional formatting conflict

cpcp

New Member
Joined
Jul 27, 2010
Messages
5
Hello all I am new here and have tried searching for an answer with no success.

I am trying to format text colour dependant on A Date Occurring such that the text turns to yellow for This Week then orange for Tomorrow and then red for Today.

The problem I encounter is that as Today and Tomorrow are subsets of This Week strange formatting behaviour seems to occur.

What am I not understanding here?

Thanks for any pointers.
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

PA HS Teacher

Well-known Member
Joined
Jul 17, 2004
Messages
2,838
The order of your conditions matters.

How about trying:

Make your 1st condition <= Today.
Make your 2nd condition <= Tomorrow.
Make your 3rd condition <= 1 Week from Today.
 

cpcp

New Member
Joined
Jul 27, 2010
Messages
5
Thank you that worked but it illustrated another stumbling block for me unfortunately :mad:

The field This Week seems defined to be up until midnight on Saturday night. There is a field for In The Last 7 Days; is there a field or work-around for In The Next 7 Days?

Sorry for the trouble.
 

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,394
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
Hello all I am new here and have tried searching for an answer with no success.

I am trying to format text colour dependant on A Date Occurring such that the text turns to yellow for This Week then orange for Tomorrow and then red for Today.

The problem I encounter is that as Today and Tomorrow are subsets of This Week strange formatting behaviour seems to occur.

What am I not understanding here?

Thanks for any pointers.

Try to rearrange the Conditional formats Order.
Make the one for This week the first condition.
If you're using Excel2007 - there is another option - "Stop if true".
The field This Week seems defined to be up until midnight on Saturday night. There is a field for In The Last 7 Days; is there a field or work-around for In The Next 7 Days?
what are the formulas used for this. the first/last day of the week is defined in the formula (default is 1=Sunday/Saturday)(2 is for Monday/Sunday ...)
What kind of FIELD for the last 7 days?
 
Last edited:

cpcp

New Member
Joined
Jul 27, 2010
Messages
5
Hi Bobsan42,

The first part of my question is answered well -thanks for your input.

The second part of my question refers to the drop down options in 2007 under: Format>Conditional Formatting>Highlight Cell Rules>A Date Occurring.

Perhaps you are telling me there is a way to make my own rules via another method that I haven't seen in 2007?

Thanks
 

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,394
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
sorry - no 2007 around (work)
may be in the evening (at home ) :)
You mean that in the menu there is a Ready Conditional Formatting for the last 7 days?
if there is none for the next 7 day - make a cond.format by cell value (or formula):
>= Today() And <=today()+6
(not ready or copy/paste - sorry)
 

Watch MrExcel Video

Forum statistics

Threads
1,133,277
Messages
5,657,801
Members
418,414
Latest member
ECMdusty

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
Top