How to change a cell colour when a date is 11 months and then a year old

Use conditional formatting, with a formula as the rule.

Rule 1: format red: Formula =$A2<=EDATE(TODAY(),-12)

Rule 2: format yellow: Formula =$A2<=EDATE(TODAY(),-11)

A2 is used as an example range, this should be the cell that conatins, or will contain the date in question.

If you're not familiar with conditional formatting then there is an introduction / tutorial here that will make more sense than I can http://www.contextures.com/xlCondFormat01.html

If you have not got the Edate formula available to you try
=NOW()>=DATE(YEAR(G6)+1,MONTH(G6),DAY(G6))
=NOW()>=DATE(YEAR(G6),MONTH(G6)+11,DAY(G6))

Thanks Jason & Barry. Will try them later and report back. Thanks for all your help

I still dont seem to be able to complete it. Are the examples you gave for Excel 2007? Its probably me being a bit slow but my screen doesnt seem to match the ones shown and i cant seem to be able to add conditions. When i click on conditional formatting i get a few options. If i click on "Highlight cell rules" do i then click on "more rules"? I might just give up on the task as i'm finding it extremely frustrating :-(

Yes, the instructions apply to 2007.
When you click on CF choose the New Rule option and from that screen choose the Use Formula option.
You can also get there by choosing More rules from the Highlight Cell Rules.

Thank you. What do i put into the box that says "format values where this value is true" please? I am trying to format column C. Do i highlight all the cells that i need to format or just the first one, in this case C2?

I assume you mean: "Format values where this formula is true"

That's where you put the formula suggested by Jason.b75 in post #2. Adjusting the ref, as he said, from $A2 to whatever your start cell ref is.
You can either highlight the range before applying the CF or do it in the Applies To field which will appear when you select your format choice(s) and click OK.

Note that the 2 rules must be in the proper sequence, as given in post #2.

That's brilliant, my cell now turns red when the date is over a year old. Next part is where do i put the 2nd rule as it never gave me the option todo that anywhere on the screen. It let me format it with the first rule but there wasn't anywhere to add another rule. Thanks for all your help so far

Just repeat the process, the new rule will be added after the existing one.

Or you can do it in the "manage rules" window from the conditional formatting menu.

Thank you very very much indeed, it works a treat. I have a couple of books on Excel but none of them are any good. I think i need to enrol on a course and do some practice

Glad you got it working, davezb. If you are satisfied with the solution(s) provided, please remember to mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save