Conditional formatting issue

[RDOREMUS]

I HAVE AN ISSUE WITH A SPREADSHEET TO WHERE I HAVE ALL THE CONDITIONAL FORMATTING FORMULAS ENTERED BUT WAS ASKED TO ADD A NEW COLUMN WHICH CHANGES UP SOME OF THE RULES. WHAT I HAVE IS 5 COLUMNS, B, C, D, E, F.
B = AN INSPECTED DATE
C= A DATE THATS 30 DAYS AFTER THE INSPECTED DATE (B)
D= A DATE THATS 60 DAYS AFTER THE INSPECTED DATE (B)
E= A DATE THATS 90 DAYS AFTER THE INSPECTED DATE (B)
F- IS A DATE WHEN ENTERED MANUALLY WOULD GIVE COLUMN (B)'S DATE AN EXTRA 3 YEARS, WHICH SHOULD CHANGE THE COLORS AND DATES IN C, D, AND E. BUT WHEN COLUMN (F) IS BLANK THEN NO CHANGES OCCUR.

Inspected       30 Days    60 Days      90 Days        Decal issue date
       
        27-Aug-09      26-Sep-09   26-Oct-09   25-Nov-09     31-Aug-09

WHAT IVE TRIED IS INSPECTED DATE =SUM(DECAL ISSUE DATE + 1155) BUT WHEN I DO THAT IT MAKES THE 30, 60 AND 90 COLUMNS ALL CHANGE TO NO COLOR WHEN I WANT THEM TO BE GREEN. ANY HELP WOULD BE GREATLY APPRECIATED.
THE CONDITIONAL FORMATTING FORMULAS ARE AS FOLLOWED:
=IF(DATEDIF($B2,TODAY(),"d")<=30,TRUE,FALSE) - GREEN
=IF(DATEDIF($B2,TODAY(),"d")<=60,TRUE,FALSE) - YELLOW
=IF(DATEDIF($B2,TODAY(),"d")<=90,TRUE,FALSE) - ORANGE
=IF(AND($B2<>"",DATEDIF($B2,TODAY(),"d")>90),TRUE,FALSE) - RED
=IF($B2="",TRUE,FALSE) - BLANK

 

[GlennUK]

You logic is flawed, I'd say ... ( and you ARE TYPING IN UPPERCASE ... DON'T DO THAT ). If an Inspected Date is entered, how can that also be a calculated value from the Decal Issue Date?

 

[RDOREMUS]

Yea ive realized its pretty flawwed. .. im having trouble writing new formulas so that i can incorporate the column with the decal issue date so that it reflects on C,D,E. =IF($B2="","",30+IF(G2="",B2,EDATE(B2,36))) ive been working with that formula and it changes it all to green but for some reason its ALL green haha. any suggestions?

 

[GlennUK]

Yea ive realized its pretty flawwed. .. im having trouble writing new formulas so that i can incorporate the column with the decal issue date so that it reflects on C,D,E. =IF($B2="","",30+IF(G2="",B2,EDATE(B2,36))) ive been working with that formula and it changes it all to green but for some reason its ALL green haha. any suggestions?

Where are you using that formula?

 

[RDOREMUS]

=if($b2="","",b2+30)
=if($b2="","",c2+30)
=if($b2="","",d2+30)

i use those formulas to get the dates in c,d and e.

=and(c2<>"",today()<=$c2) = green
=and(c2<>"",today()<=$d2) = yellow
=and(c2<>"",today()<=$e2) = orange
=and(c2<>"",today()>$e2) = red

those are my cf formulas and i was using the formula i sent you via the reply before i was replacing the formulas in column c with that in hope that it will format c,d and e if column f has an x.

 

[GlennUK]

And when you use the new formula, does that give you the dates that you want when there's an "x" in column F? ( sorry, but I don't understand what you mean when you say "it changes it all to green but for some reason its ALL green" )

 

[RDOREMUS]

Well yes and no. . All the dates turn green regardless if theres an x in column f or not. Like its totally disregarding the x and just changing all dates to green. I was hoping that that formula would make it to where if the user inputs an x or takes one out, that it would change the conditional formatting for columns c:e. But its not doing that, it just changes c:e all green regardless of whats in column f. I hope that helps some with the understanding.

 

[GlennUK]

OK, I'm really not understanding this at all now. The new formula that you showed was the cell formula for column C? Wasn't it?

 

[RDOREMUS]

Yes im inputing that formula into c, which if it works will make d and e change accordingly as well.

 

[Salar]

Read this from the web - I found it a great help.

http://www.mvps.org/dmcritchie/excel/condfmt.htm

Salar