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

 

[EDSTAFF]

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.

I'm not trying to conditional format by the expiration dates in Renewal Date (Column F). I "may" conditional format that column via the Expiration Date later but that is a totally different issue. It should give similar color results as conditional formatting in Column J when I get it to work properly. Conditional Formatting in Expiration Date Date would also negate the need for Column J but I want user to be able to see time before expiration and also have it color-coded.

I'm trying to conditional format via color, the text string results of Column J. Most of it works except for the oddity of Excel placing months 10 and 11 in the <6 month period (based on color) rather than the appropriate >6 month period. I'm tryng to use a formula to properly extract data from that string, sort it sequentially by number (1-12 months) and conditionally format it based on that extraction and color the cell based on the format rules of conditional formatting. It can be done but seems like it going to be a challenge to come up with a formula to do precisely that.

Thanks for your input,

L.M.

 

[Norie]

Column J returns a string that is the difference between 2 dates.

The string in J is based on dates, so why not use those dates in the conditional formatting.

 

[EDSTAFF]

Column J returns a string that is the difference between 2 dates.

The string in J is based on dates, so why not use those dates in the conditional formatting.

I might end up doing that as I haven't come up with a formula that properly extracts the numbers from the text string in column J and sorts them sequentialy. I might consider using MID/ISNUMBER/MATCH/COUNT as part of the formula. Performing the formatting using dates, I'm sure, would accomplish what I want but I'm determined now to find a way to extract the numbers, sort them correctly and do the conditional formatting as desired. Perhaps I'm stubborn but I want to learn how to accomplish my goal by using extraction of the text string somehow.

Thanks,

L.M.

 

[Norie]

Well I wish you luck.

I don't really think its the best approach.

You already have the dates available and you can use them to (numerically) calculate the difference using a formula.

You could then use that formula in your conditional formatting, with the Formula Is option.

 

[Peter_SSs]

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.

I copied your J column formula from the board to my sheet and applied the Conditional Formatting I described, and it worked. If you copied my Conditional Formatting formulas (for conditions 2 and 3) and pasted them into the Conditional Formatting dialog boxes, rather than typing them, you might find that additional " have been added. If you still have my Conditional Formatting applied somewhere, or apply it again now, go back in and have a look at the Conditional Formatting formulas. When pasted in, they may look like this:

="AND(0+LEFT(J11,FIND(""years"",J11)-1)<1,0+MID(J11,FIND("","",J11)+1,FIND(""months"",J11)-FIND("","",J11)-1)<6)"

You might find that additional " have been added near the front, at the end, and all the others in the middle doubled up. Try removing these extra " and the formatting should work.

 

[EDSTAFF]

Thank you Peter!

I knew this could be done.

It is working now. I didn't realize " had been changed to "" and thereby crippling the formula. This will save me countless hours is having to redo the mega formula in J Column or adding nesting formulas, ISERROR, ISNUMBER, etc. I might consider using a different formula next time for the date difference calculations, like DATEDIF or some other such function.

I originally had the J Column to simply return months which was much simpler both in formula and in doing Conditional Formatting. I guess the more complicated the formula, the more chances for problems to arise as in this scenario.

One further question: Am I correct in "assuming" that in text strings, 1,10,11 or 2,20,21, etc are considered by Excel to precede higher text string numbers like 6? Is that the reason months 10 and 11 were grouped by conditional formatting as preceding month 6 and thereby grouping it with the 1-6 month category in conditional formatting? Could this scenario be avoided by created a formula that would return a value of 01months-09months so "0", and NOT 1 was the first number digit in the text string return of J column? Just curious so I can keep that in the old memory bank for future reference.

Again, thank you so much for correcting this issue for me. I'm very appreciative!

L.M.

[quote="Peter_SSs"
I copied your J column formula from the board to my sheet and applied the Conditional Formatting I described, and it worked. If you copied my Conditional Formatting formulas (for conditions 2 and 3) and pasted them into the Conditional Formatting dialog boxes, rather than typing them, you might find that additional " have been added. If you still have my Conditional Formatting applied somewhere, or apply it again now, go back in and have a look at the Conditional Formatting formulas. When pasted in, they may look like this:

="AND(0+LEFT(J11,FIND(""years"",J11)-1)<1,0+MID(J11,FIND("","",J11)+1,FIND(""months"",J11)-FIND("","",J11)-1)<6)"

You might find that additional " have been added near the front, at the end, and all the others in the middle doubled up. Try removing these extra " and the formatting should work.[/quote]

 

[barry houdini]

I tend to agree with Norie

could you not simply have your 2nd condition as

"formula is"

=DATEDIF(G11,H11,"m")>5

and the third condition as

"formula is"

=DATEDIF(G11,H11,"m")<6

 

[EDSTAFF]

I tend to agree with Norie

could you not simply have your 2nd condition as

"formula is"

=DATEDIF(G11,H11,"m")>5

and the third condition as

"formula is"

=DATEDIF(G11,H11,"m")<6

Your formula, slightly modified, gives the correct formatting as well. I changed formula to:
=DATEDIF(H11,G11,"m")>5
=DATEDIF(H11,G11,"m")<6

I'm VERY new to date calculations so I'm fllying by the seat of my pants to do these calcuations and know which functions to use and get the results I want. Is there an easier formula than the mega formula I used in Column J to get the time period in Y/M/D of time until expiration? That one took hours and hours to build and perfect to achieve the results I wanted. If there is a shorter method I would certainly like to know and keep it for later use when needed. Is there any way to achieve the same results without the formula returning text string values as in this file?
I considered using different columns for Y/M/D but that itself would create a nightmare as conditinal formatting for each column would, conceivably, return different colors via conditional formatting. For instance, 0y/8m/0d would have yellow/green/yellow if same rules applied across all columns. I quickly gave up on that idea when I realized what would happen when formatting via color so I was left with only this option. I suppose some formula could be written so if y=0, m>6-->green,if y=0,m<6-->yellow, if y>0,green,if y<=0,m<=0,red but I didn't want to tackle that issue.

Your method is certainly easier that number extraction from a text string. As I metioned before I am new to this type of calculation via Excel so I'm learning something new everyday. Your method furthered my knowledge of Date formulas and Peter's introduced me to text extraction of strings. Looks like I've got a lot to learn before I take more advanced math and computer science courses!

Again, thanks for your help in getting the formatting to work. I'll store the formula away for future use and consider it a lesson learned.

Now if I can just get the **** List sorting to work across multiple sheets I'll be a happy camper and finish this project!

L.M.