Conditional Formatting - Dates I would like to be able to enter a date in excel

Question

Conditional Formatting - Dates

I would like to be able to enter a date in excel and for it be green for 11 months then turn amber then turn red the following month.

eg.

12/09/14 to be the day I have done something and it is ok for 12 months before I have to look at it. I want to know a month before it needs to be looked at to get ready so the date needs to turn amber and if I leave it until the 12 months are up then it turns red to tell me.

I have to record the day that I do the job for records.

Can any one help??

Explanation / Answer

Assuming your date is in A1 -

Home tab > Conditional Formatting > New Rule > Use a formula to determine which cells to format and put following formula and choose Fill Colours as below (Repeat for Amber and Red also) -

Green: =DATEDIF(A1,TODAY(),"m")<=11

Amber: =AND(DATEDIF(A1,TODAY(),"m")>11,DATEDIF(A1,TODAY(),"m")<=12)

Red: =DATEDIF(A1,TODAY(),"m")>12

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.