Conditional Formatting Dates

[Frogggg]

I know there are quite a few conditional formatting posts. I have been through a few of them and I can't make it work properly.

In column D I have a list of dates that was the day a document was approved. This document needs reviewing every three years. What formula do I use if I want the date to turn red if the date in column d is more than 3 years ago?

 

[pedro-egoli]

Conditional format as
Formula is =D1<TODAY()-1095

Do conditional formatting whilst in D1 (or change formula to whatever cell you start in) and then Edit/Copy , highlight the range you wish added to conditional format and Paste Special/Formats

Pedro

 

[bertie]

Try this:

Assuming 365 days in the year, then 3*365=1095 days in three years.

From the conditional formatting dialog box:
Select Formula Is from the condition drop down box.
Type in:
=TODAY()-D2>=1095

Click the Format button and set the Pattern to Red.
Copy and PAsteSpecial the format over desired range.

 

[pedro-egoli]

For some reason my post does not show the complete formula.

In simple terms it is

d1 less than or equal to Today()-1095

If someone can explain why only the D1 shows up in original reply please do.

Pedro

 

[Matt Rogers]

Hi.

The date is in D1
Formula: =today()>=date(year(D1)+3,month(D1),day(D1))
D1 is colored in 3 years

(switch system date + 3 years & and restart the sheet)

 

[Frogggg]

Thanks guys this problem is solved. I was dancing around a few formulas but couldn't quite get it right.

Screwdriver your formula works a treat. I appreciate everyone's help. You make life so easy!!!! Have a great day, I know I now will!

 

[Matt Rogers]

Hi Frogggg.

Many thanx for your feedback.
I wish you all the best.

 

[Frogggg]

Hi guys again. I want to build on that original formula. Originally my dates were in column D, I now want to change the formula based on a text rating in column E. I have been trying some nested if statements, but I just can't get it right again. I really need to go on a course to understand formulas!!!!

Basically it works like if the text in column E says Low or Moderate then the date in column D will turn red after the 3 years. If the text in column E says high or critical then it will turn red after 1 year.

 

[Frogggg]

This is what I have so far:

=if(E5="Low",today()>=date(year(D5)+1,month(D5),DAY(D5))),0

then I was going to run it again for the other criteria, however it just doesn't do the formatting. I am not getting the errors I had before but its not changing my formatting.

 

[Frogggg]

I have also tried AND((TODAY()>=DATE(YEAR(D4)+3,MONTH(D4),DAY(D4)),E4="Low") but that doesn't work either.