Conditional Formatting

[BSal]

Hey there. I'm a newbie to excel and can't find anything to help me. Here's my dilema:

Mr. Snuffy completes a course on 13AUG08 and hands me a certificate.
I put the complete date of that certificate in a cell.
When I open excel, from 14AUG08 until 13MAY09 (9mos) I want it to stay white.
On 14MAY09 (or 3 mos from expiration) I want the cell to turn yellow.
On 14AUG09 I want the cell to turn red.

Can anybody please help! I need step by step directions because like I said, I'm a total Noob.

Thank you so much for your help!!! :D

 

[barry houdini]

Hello BSal, welcome to MrExcel

If your date is in A1 then select that cell and use

Format > Conditional Formatting > "formula is"

and set these conditions

condition 1
=(DATEDIF(A1,TODAY(),"y")>0)*(A1<>"")
select red pattern

condition 2
=(DATEDIF(A1,TODAY(),"m")>9)*(A1<>"")
select yellow pattern

OK

Note: if you have a column of dates, e.g. in A1:A100 then you can select that range to start with and set the same conditions [formula should refer to the first cell in the range]

 

[Andrew Fergus]

Note: if you have a column of dates, e.g. in A1:A100 then you can select that range to start with and set the same conditions [formula should refer to the first cell in the range]

In addition to Barry's suggestion you can also copy > paste special (format) to copy the conditional format from one cell to another.

 

[Gerald Higgins]

deleted

 

[BSal]

Wow. Thanks You guys are awesome. And freakin quick! But I forgot to add, I'm using Excel 2007. I'm so sorry about that. Like I said, I'm a noob.

 

[barry houdini]

You can do pretty much the same thing in Excel 2007

Select A1

In "Home" select "Conditional formatting" from the ribbon

Select "New Rule" and

"Use a formula to determine which cells to format"

Apply second formula as per previous post

select required formatting

Now do the same for the other rule