Conditional Formatting or IF using Dates

Solved/Closed
TS - Nov 18, 2009 at 11:24 AM
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Feb 12, 2010 at 09:01 AM
Hello,

I am trying to change the colour of an entire row to 3 different states depending on the result.

The row needs to stay clear if the date in a particular cell (say B2) is after TODAY

or

I want the row to change to say orange if the date in a particular cell (say B2) is Today but only if another cell (say C2) is blank.
If the the other cell (C2) isn't blank then I want it to stay clear.

or

The row needs to change red if the date in a particular cell (say B2) is before TODAY but only if another cell (say C2) is blank.
If the other cell (C2) isn't blank then I want it to stay clear.

I think that makes sense, ineffect, I am wanting to highlight items that are due today, overdue and leave clear if not due yet?

I hope someone can help.

2 replies

sharpman Posts 1021 Registration date Saturday May 23, 2009 Status Contributor Last seen October 20, 2010 183
Nov 18, 2009 at 05:29 PM
This is how it can be achieved in excel 2007.

Highlght a row that you wish to format (or highlight all the data on that row only)
select 'conditional formatting' from the ribbon menu
Manage rules
New rule
for the first one you need to select the cell that the date is in that you wish to action.
in my case I am using A2 and B2

create the following rules (x3 in any order)

=$B$2>TODAY() then format this any colour you like. this means if th date in cell b2 is later than today format the line with the colour selected.

=$B$2<TODAY() Then format the colour required. This means if the date is before todays date then format the row with the colour selected.


=$B$2 = TODAY() then, again format the colour desired. This means that if the date in cell b2 is todays date the colour selected colour

then create the next rule as follows

=$A$2="" then select the clear/white for the colour. this means if cell a2 is empty then colour the row clear/white. But you must also tick the stop if true box at the end of rule.

what this will do is if cell a2 is blank then the rest of the rules are ignored.

Hope you can understand/decipher that.

hope it helps.
20
Sharpman,
Your answer gave me some light. However, I would lik to take the conditional format a step further by allowing the conditional format to apply using the system date on various dates. I am intending to do the following;
It is a training chart built on date timing such that 3 months before approaching 12 months from the initial training date the cell should flag yellow, 2 months before approaching 12 months from the initial training date the cell should flag red.

Please advise me on how to conditionally format without me having to track the dates but for the system dae to do it for me.

Regards

Ishmael
0
I'm entering dates staring in Column K Row 2 as to when I email a customer. If they don't reply within three days I need Column M Row 2 to turn Orange, after 5 days turn Red. If no date is entered in Column K, I need Column M to stay white.

So the color in column M is dependent upon the entered date in K.
0
Hi,

I read your answer to a solution using date to in conditional formatting to highting cells. But my problem ois that I have date only in one column, So in short if its sat or sun I want that whole row to appear in another color I mean the entire ROW
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Feb 12, 2010 at 09:01 AM
Answer given by sharpman is exactly what you looking for. Read his answer again. He is using today, you surely can change today to sunday and saturday.
0