Conditional Formatting

[gmazza]

Hi there,
I am trying to do some conditional formatting but am having some problems.
I have a bunch of dates, all different, in column A, that range from March 15, 2009, to March 15, 2011.

I want these fields to turn red when a year past their date has happened.
So the field March 15, 2009 would be already red, and the field March 15, 2011 would not be read until March 15, 2012.

How can I apply this to ALL my dates in column A.

I tried myself but with the rules in conditional formatting, there is nothing for what I am trying to say.
I am trying to say IF Today() > A3 + 365 then make it red, else, don't.

Any help is appreciated!

[jwright650]

see if this is what you need. see attached

edit:
your condition should read:

Today() <= A3 - 365 because the dates are older(smaller) than Today's date

I placed Today() in F1 and formatted the entire Column A with a formula like above

[gmazza]

Thanks for your reply John, that is exactly what I needed!
Have a good weekend!

[jwright650]

Thanks for the feedback...glad it worked for you.

edit: don't forget to mark the thread solved so others using search can search the solved threads for answers to their questions.

[Cutter]

For the sake of accuracy (because 25% of the time it will be 366 instead of 365) you might want to consider this formula for your CF:

=TODAY()>=EDATE(A3,12)

Here's a link for EDATE() if you're not familiar with it:

http://www.excelfunctions.net/Excel-Edate.html

[gmazza]

Thanks for that!
A different problem I am having though. I have my CF set to format only cells less than =TODAY()
and I did it for 80 columns I have and it works for all of them except a few. One of the dates is 16/05/10, another is 27/09/08, which are clearly less than today's date so is there a reason why it works for some and not others? Thanks!!

[Cutter]

Make sure the dates are actually dates and that Excel is interpreting them properly.

Click on the cell that holds 16/05/10 and see what shows in the formula bar. Is it 2010-05-16 or 2016-05-10?