Red-Amber-Green formatting for dates

[BlueDingo]

Hi Guys, hope you can assist my Friday brain.

In a spreadsheet I have contracts that need highlighting for attention.

Cell G2 has the obligatory =TODAY()

Cells i6:i400 have a "next update required" revealing all the dates.

I require:
Red for those dates that have reached and exceeded "today" (G2)
Amber for those that are 10 days prior to turning red
Green for the remainder

Thank you.

 

[Michael M]

Hi BlueDingo
Had to have a little laugh.....Rural Sydney ???....come out west and be really rural....LOL !!

Highlight the range of cells in column "I"
The use Conditional Formatting for
1. Green.......Cell value Is.....Less Than...=$G$2-10
1. Amber.......Cell value Is.....Between...=$G$2-10 and $G$2
1. Red.......Cell value Is.....Greater Than or Equal to...=$G$2

Format each one as required

 

[BlueDingo]

Michael of Not Rural Sydney

All works well except the Amber, if I input 8Aug11 it shows up as green.

Any ideas?

Cheers Champ, Pete

 

[Michael M]

Pete
It works fine for me....
A couple of things to check
1. Type it in as a date in consistent format
2. check your formula in the Amber CF
3. It may not matter but, I used the conditons in CF in the Red , Amber, Green order !!

 

[BlueDingo]

Checked all things, date formatting etc but this is what I'm getting:
<TABLE style="WIDTH: 71pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=94 border=0 x:str><COLGROUP><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3437" width=94><TBODY><TR style="HEIGHT: 51.75pt" height=69><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 71pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 51.75pt; BACKGROUND-COLOR: silver" width=94 height=69>Next Update Due</TD></TR><TR style="HEIGHT: 25.5pt" height=34><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BACKGROUND: red; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 25.5pt; mso-ignore: style; mso-pattern: auto none" width=94 height=34>N/A</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BACKGROUND: #ccffcc; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; mso-ignore: style; mso-pattern: auto none" width=94 height=17 x:num="40375">16-Jul-10</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BACKGROUND: #ccffcc; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; mso-ignore: style; mso-pattern: auto none" width=94 height=17 x:num="40593">19-Feb-11</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BACKGROUND: #ccffcc; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; mso-ignore: style; mso-pattern: auto none" width=94 height=17 x:num="40334">05-Jun-10</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BACKGROUND: #ccffcc; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; mso-ignore: style; mso-pattern: auto none" width=94 height=17 x:num="40586">12-Feb-11</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BACKGROUND: #ccffcc; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; mso-ignore: style; mso-pattern: auto none" width=94 height=17></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BACKGROUND: #ccffcc; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; mso-ignore: style; mso-pattern: auto none" width=94 height=17></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BACKGROUND: red; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; mso-ignore: style; mso-pattern: auto none" width=94 height=17 x:num="40770">15-Aug-11</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BACKGROUND: #ccffcc; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; mso-ignore: style; mso-pattern: auto none" width=94 height=17></TD></TR><TR style="HEIGHT: 25.5pt" height=34><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BACKGROUND: #ccffcc; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 25.5pt; mso-ignore: style; mso-pattern: auto none" width=94 height=34 x:num="40641">08-Apr-11</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BACKGROUND: #ccffcc; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; mso-ignore: style; mso-pattern: auto none" width=94 height=17></TD></TR><TR style="HEIGHT: 25.5pt" height=34><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BACKGROUND: #ccffcc; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 25.5pt; mso-ignore: style; mso-pattern: auto none" width=94 height=34></TD></TR><TR style="HEIGHT: 25.5pt" height=34><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BACKGROUND: #ccffcc; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 25.5pt; mso-ignore: style; mso-pattern: auto none" width=94 height=34></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BACKGROUND: #ccffcc; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; mso-ignore: style; mso-pattern: auto none" width=94 height=17></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BACKGROUND: #ccffcc; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; mso-ignore: style; mso-pattern: auto none" width=94 height=17></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BACKGROUND: red; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; mso-ignore: style; mso-pattern: auto none" width=94 height=17 x:num="41010">11-Apr-12</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BACKGROUND: red; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; mso-ignore: style; mso-pattern: auto none" width=94 height=17 x:num="40992">24-Mar-12</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BACKGROUND: #ccffcc; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; mso-ignore: style; mso-pattern: auto none" width=94 height=17></TD></TR><TR style="HEIGHT: 26.25pt" height=35><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BACKGROUND: red; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 26.25pt; mso-ignore: style; mso-pattern: auto none" width=94 height=35 x:num="40780">25-Aug-11</TD></TR></TBODY></TABLE>

Note that 15Aug11 should be showing Amber, and all dates prior to today's date should be red.

I have also placed in order of R-A-G

 

[Michael M]

"fraid to say then.....it's gotta be the way you're setting it up.
Send me a PM with your e_mail address and I'll send you the workbook I did mine on, and you can compare notes.

When you set up the CF did you highlight from "i6:i400" before you started the CF ?