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.
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

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,343
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,343
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)
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,561
Messages
5,512,071
Members
408,878
Latest member
stams

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top