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.

 

[oldbrewer]

you need a helper cell with todays date achieved by =today() say it is in cell Z1
in the cell with date of last training say it is in D3
you need to have a formula in cond formatting =z1-d3 < 60 and apply yellow color
in the second formula =z1-d3 < 1 and apply red

 

[Marcelo Branco]

Is this what you need?

Dates 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/10/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]


M.

 

[kallabungo]

Unfortunately that's not quite what i'm looking for, thanks for your help though.

 

[kallabungo]

Is this what you need?

Dates 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/10/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]


M.

<edate(today(),2))
<edate(today(),2))[ code]

Yes this is what I need and is easy enough to achieve when dates are expiry dates.

The trouble is that the date im entering into the cells is the date of issue - these wont expire for a further 3 years. Everything I read tells me to use the =TODAY function but I cant see that helping.

Ideally I need some formula that reads the date in cell D3 then in a further 34-36 months it will change the cell yellow.

I've tried all sorts of combinations and came close but couldn't quite get it to work.

<edate(today(),2)) this="" is="" the="" closest="" i've="" managed="" to="" get="" so="" far="" but="" it="" still="" doesn't="" quite="" work<edate(today(),2))="" -="" best="" come="" up="" with="" work="" how="" i="" want="" to.<="" html=""></edate(today(),2))></edate(today(),2))[></edate(today(),2))

 

[kallabungo]

<edate(today(),2))
=AND(D3>=TODAY(),D3 <EDATE(TODAY(),2))<edate(today(),2))< html=""></edate(today(),2))<></edate(today(),2))

 

[kallabungo]

I tried to paste the formula i'm currently using but it's only allowing me to paste half of the code...

 

[oldbrewer]

say training is given on April 1st 2018, 3 years after that is April 1st 2021. So you need to compare today's date with April 1st 2018 plus 3 years.

Let April 1st 2018 be in A1

In A1 cond format =today()-A1 > 1095 and set color = red

and of course cond format 2 is =today()-A1 >1035 and set color to yellow

 

[kallabungo]

say training is given on April 1st 2018, 3 years after that is April 1st 2021. So you need to compare today's date with April 1st 2018 plus 3 years.

Let April 1st 2018 be in A1

In A1 cond format =today()-A1 > 1095 and set color = red

and of course cond format 2 is =today()-A1 >1035 and set color to yellow

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!

 

[kallabungo]

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!

Ok so the formatting does seem to be working ok now.

Now my trouble is that 1 column has a certificate that expires in 3 years, the next column expires in 1 year. For some reason the formatting I'm applying to column D also seems to be applying to column E, although I have specifically made sure i cleared all the rules, highlighted just the cells in the correct column and created new rules.

So column D is setup as above

Column E is as;
=ISBLANK(D3)=TRUE - this is at the top, keeping cells white while valid with the "stop if true" box ticked
=TODAY()-D3>275 - this is the yellow format which is the next row down
=TODAY()-D3>365 - this is the red format which is the last row

I'm finding that even with no data in column E, when i enter something in column D it changes the colours of the cells in both columns...