Conditional formatting nightmare!

[kallabungo]

Hi Gurus,

I've been trying and failing for too long at this so i'm here for some help.

I'm making a training matrix that uses conditional formatting to alert me when training is about to and has expired.

So for example,

on 01/01/18 Mr. Smith took abrasive wheels training. The training will expire in 3 years.

I wish for the cell values to remain white when conditions are normal,
Turn yellow when the date that training was taken is approaching 60 days from the 3 year expiry (01/11/20)
Turn red on the day of expiration and remain red until a new date has been entered (training refreshed)

I know it would be a lot easier to do this if i entered expiration dates of the training rather than issue dates but this is the way that would work best for me.

Appreciate any help i may receive.

 

[kallabungo]

Ha ha ha... Number 1 rule, don't copy and paste formulas... I've spotted my mistake!

 

[Marcelo Branco]

AHA! Now we're getting somewhere...

The only trouble i'm seeing now is that the formatting overrules each other.

If i set my red formula at the top it makes all the dates stay red,
If i set yellow at the top it makes all the dates stay yellow...

How do I get around this?

Currently in my rules manager I have
=ISBLANK(D3)=TRUE - this is at the top, keeping cells white while valid with the "stop if true" box ticked
=TODAY()-D3>1035 - this is the yellow format which is the next row down
=TODAY()-D3>1095 - this is the red format which is the last row

Many thanks for your help so far!

Maybe this
Dates as dd/mm/yyyy

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Name​

[/TD]
[TD]

Date​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

John Smith​

[/TD]
[TD]

01/01/2018​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

Bob​

[/TD]
[TD="bgcolor: #FF0000"]

01/12/2014​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

Charles​

[/TD]
[TD="bgcolor: #FFFF00"]

01/10/2015​

[/TD]
[/TR]
</tbody>[/TABLE]


Select B2:B4 and apply Conditional Formatting using formulas

Rule 1 (approaching 60 days)
insert this formula
=AND(B2<>"",60>=EDATE(DATE(YEAR(B2)+3,MONTH(B2),DAY(B2)),-2)-TODAY(),EDATE(DATE(YEAR(B2)+3,MONTH(B2),DAY(B2)),-2)-TODAY()>0)
Format: Fill --> yellow

Rule 2 (today greater than date of expiration)
=AND(B2<>"",TODAY()>EDATE(DATE(YEAR(B2)+3,MONTH(B2),DAY(B2)),-2))
Format: Fill --> red

Hope this helps

M.

 

[oldbrewer]

cells will stay white UNTIL the rules make them red or yellow

when you refer to last row or next row down I hope you mean first rule, second rule....

=today()-d3 > 275 you say is the yellow format
so if training was done yesterday today()-d3 equls 1 and you do not want the cell yellow

have a good think then come back - maybe I am wrong...

 

[kallabungo]

cells will stay white UNTIL the rules make them red or yellow

when you refer to last row or next row down I hope you mean first rule, second rule....

=today()-d3 > 275 you say is the yellow format
so if training was done yesterday today()-d3 equls 1 and you do not want the cell yellow

have a good think then come back - maybe I am wrong...

It all works exactly how I wanted it to now, thank you so much for your help.

The problem I had was the red rule was below the yellow so the yellow was taking over, putting red rule in the middle and yellow at the bottom cured the problem.

The other thing I had when the additional columns were also changing was because I copied and pasted the formula into the next columns without changing the cell reference. Changing the cell reference brought everything in to place.

Mission accomplished! Thanks for all that have contributed.