Changing a cell colour when the date is over a year old

[davezb]

I am using excel 2007 and trying to set up a spreadsheet where i can enter a date when a course has been taken and then 11 months after that date the cell would change to yellow and 1 year from the original date it would turn red. Anybody help me with this please?
I'm a new member so hi to all

[dmang1]

Hello Dave,

I would use the Conditional Formatting feature found on the Home tab.

Add two dates to your workbook, one with the 11 month date eg: =EDATE(TODAY(),-11) and a second with =EDATE(TODAY(),-12)
Highlight the column/s you want to apply the formatting to and go to Home > Styles grouping > Conditional Formatting.
Select Highlight Cell Rules > Less Than and then click on the second new formula and in the With box select Custom Format and choose a Red Fill on the Fill tab and Black font on the Font tab.

You need to repeat this process for the first additional values but select the Highlight Cell Rules > Between option and select the two relevant cells =first cell reference and =second cell reference with a Yellow fill.

I hope this is a simple solution to your formatting issue.

Cheers,
Diana

If this post has been helpful you can click the star to the left of this post.

[davezb]

Thank you very much for you're help Diana

[stubbsj]

There is an easier way using what Diana gave you without having to add additional columns.

Set up 2 conditional format statements, each "Use a formula to determine which cells to format"
1) use the formula =EDATE(TODAY(),-12)>=A1 where "A1" is the cell you want to use, select format, set fill to "Red"
2) use the formula =EDATE(TODAY(),-11)>=A1 where "A1" is the cell you want to use, select format, set fill to "Yellow"

Hope this helps