Conditional Formatting or IF using Dates

Solved/Closed
Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
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

Posts
1021
Registration date
Saturday May 23, 2009
Status
Contributor
Last seen
October 20, 2010
182
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
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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