Conditional Format Issue...wrong color for 10/11 months

[EDSTAFF]

Conditional Format Rules:
#1 IF =EXPIRED-->RED
#2 IF >= 0YEARS,6MONTHS,0DAYS-->GREEN
#3 IF<0YEARS,6MONTHS,0DAYS-->YELLOW

Rule #1 works as designed. Rule #3 is not sorting by date as it should. By rule, any date over 6 months should be colored GREEN. However, anything with 10 and 11 months is wrongly colored YELLOW for some reason. I have altered the order of rules but with same effect.

Anyone have any ideas as to why this formatting problem exist and how to correct it?

Thanks,

L.M.

Staff Certifications2.xls
	J	K	L	M
10	TIMEUNTILRENEWAL(YEARS/MONTHS/DAYS)
11	EXPIRED
12	EXPIRED	RULE#1
13	EXPIRED
14	EXPIRED
15	EXPIRED
16	EXPIRED
17	EXPIRED
18	EXPIRED
19	EXPIRED
20	EXPIRED
21	EXPIRED
22	2years,0months,0days
23	1years,8months,21days
24	1years,8months,21days
25	1years,8months,21days
26	1years,8months,21days	RULE#2
27	1years,6months,21days
28	1years,6months,21days
29	1years,6months,21days
30	0years,6months,21days
31	0years,6months,21days
32	0years,6months,21days
33	1years,0months,0days
34	0years,5months,20days	RULE#3ERRORS
35	0years,5months,20days
36	0years,11months,21days
37	0years,11months,21days
38	0years,11months,21days
39	0years,10months,20days
CCRN

 

[Nimrod]

EDSTAFF:

Exactly what formula are you using for the 3 rules?

Are the values in the cells dates or string ?

If they are strings then 11month < 6month :wink:

 

[Peter_SSs]

EDSTAFF

I think that Nimrod is on the right track as it looks like your formulas in column J are returning text values.

Try these in your Conditional Format formulas:
Condition 1: as you have it
Condition 2: =AND(0+LEFT(J11,FIND("years",J11)-1)<1,0+MID(J11,FIND(",",J11)+1,FIND("months",J11)-FIND(",",J11)-1)<6) and format this yellow
Condition 3: =OR(0+LEFT(J11,FIND("years",J11)-1)>0,0+MID(J11,FIND(",",J11)+1,FIND("months",J11)-FIND(",",J11)-1)>5) and format this green

Note that I have changed the order of yellow and green conditions.

By the way, your sheet will look better on the board and be easier to read without all those things everywhere. You can avoid those by clicking the 'View Source' button in the HTML Maker (rather than the 'Please click this button to send the source to the clipbord' button) and then selecting all the resulting code to copy to your post.

 

[EDSTAFF]

This is the formula to get results in Column J (must give credit to GorD for asst with fixing the formula when it was giving error messages):

Conditional Formatting is set as:

If Cell Value is = "EXPIRED" --> RED
If Cell Value is > "0years,6months,0days"-->GREEN
If Cell Value is <= "0years,6 months,0days"-->YELLOW

Thanks for taking a look at it. I will try your solution and let you know if it fixes the problem.

As far as the "nbsp"...not sure where that came from...I'll review the FAQ's to see what happened when I posted the code.

=IF(C11=0,"",IF(E11="","EXPIRED",IF(AND(C11>0,G11>0,G11<H11),"EXPIRED",YEAR(G11)-YEAR(H11)-IF(OR(MONTH(G11)<MONTH(H11),AND(MONTH(G11)=MONTH(H11),DAY(G11)<DAY(H11))),1,0)&"years,"&MONTH(G11)-MONTH(H11)+IF(AND(MONTH(G11)<=MONTH(H11),DAY(G11)<DAY(H11)),11,IF(AND(MONTH(G11)<MONTH(H11),DAY(G11)>=DAY(H11)),12,IF(AND(MONTH(G11)>MONTH(H11),DAY(G11)<DAY(H11)),-1)))&"months,"&G11-DATE(YEAR(G11),MONTH(G11)-IF(DAY(G11)<DAY(H11),1,0),DAY(H11))&" days")))

Thanks,

L.M.

 

[EDSTAFF]

Tried your solution and set "if formula is"....on conditions 2 and 3 and inserted your formulas. Unfortunately, it it not returning green or yellow as desired but leaves the cells uncolored.

I will keep playing with to see if I can get it to work.

Thanks for pointing me in the right direction.

L.M.

EDSTAFF

I think that Nimrod is on the right track as it looks like your formulas in column J are returning text values.

Try these in your Conditional Format formulas:
Condition 1: as you have it
Condition 2: =AND(0+LEFT(J11,FIND("years",J11)-1)<1,0+MID(J11,FIND(",",J11)+1,FIND("months",J11)-FIND(",",J11)-1)<6) and format this yellow
Condition 3: =OR(0+LEFT(J11,FIND("years",J11)-1)>0,0+MID(J11,FIND(",",J11)+1,FIND("months",J11)-FIND(",",J11)-1)>5) and format this green

Note that I have changed the order of yellow and green conditions.

By the way, your sheet will look better on the board and be easier to read without all those things everywhere. You can avoid those by clicking the 'View Source' button in the HTML Maker (rather than the 'Please click this button to send the source to the clipbord' button) and then selecting all the resulting code to copy to your post.

 

[Norie]

What is the formula in column J actually calculating?

I assume it's some sort of difference between dates.

I think Nimrod is right about the string comparison.

You should probably try and use formula(s) based on those dates in the conditional formatting rather than the result of the formula in column J.

 

[EDSTAFF]

What is the formula in column J actually calculating?

I assume it's some sort of difference between dates.

I think Nimrod is right about the string comparison.

You should probably try and use formula(s) based on those dates in the conditional formatting rather than the result of the formula in column J.

Thanks for taking a look.

You are correct, it is calculating difference between dates. To be more precise, it is calculating, based on Today's date and Recertification Date, the time period remaining on staff members' various certifications. That part of the equation in Column J works correctly, with an assist from GorD. The issue I now face is getting the Conditional Formatting to look at the text string in Column J (with returns of EXPIRED, or a text string date) and change cell color to RED/YELLOW/GREEN based on the return. Unfortunately, based on my limited knowledge of Excel programming, first numerical digits are looked at before higher digits in a sequence, i.e. 1 and 11 are looked at before 6. If I am wrong, please correct me. Therefore, =Cell Value <,=,> do not work correctly as the 10,11 months are sorted before 6 and grouped with the 0-6 month period and not with the >6 month period when conditonal formatting is used. I am trying, with the suggestion of a previous responder (sorry can't recall your name, please forgive!), to use a text extract using "Formula Is", to properly extract the return and force Excel to regroup the 10,11 into the >6month category and color the cell based on that defined group of dates. I hope this is making sense! I tried the formula suggested and am tweaking it but so far can't get it to change colors as defined...it doesn't color the cells except for the "EXPIRED" rule. I might have to look at nesting formulas and ISERROR or some other way of doing the color formatting directly in the Column J formula if I can't get the suggested Condition Formatting Formula to work. I am a rookie, certainly a newbie to complex formulas, especially those involving dates, and have run out of ideas on how to get this to work as I intended.

I appreciate your input,

Thanks,

L.M.

 

[Norie]

Why do you want to look at the text for the conditional formatting?

Can't you just look at the dates?

 

[EDSTAFF]

Why do you want to look at the text for the conditional formatting?

Can't you just look at the dates?

Using dates doesn't give the proper return as, I believe, Excel regards 10 and 11 (first digit of 1) as occurring BEFORE 6 when sorting using conditonal formatting. Odd behavior for sure. That "seems" to be what is happening anyway. Therefore in conditional formatting, Excel regards 0years10months,0days as occurring before 0years6months,0days and groups the 0years10months0days in the <6months group (by color) rather than the desired >6months color. Excel sorts properly by Y/M/D when sorting by Lists and other sorting functions.

Thanks for replying,

L.M.

 

[Norie]

I mean use the dates that you are comparing, not the text result of the formula in J.

Excel regards 0years10months,0days as occurring before 0years6months,0days[/quote

Those aren't dates there strings.