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

#### EDSTAFF

##### New Member
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.

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

#### Nimrod

##### MrExcel MVP
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

##### MrExcel MVP, Moderator
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

##### New Member
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.

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

##### New Member
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.

Peter_SSs said:
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

##### Well-known Member
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

##### New Member
Norie said:
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.

Thanks,

L.M.

#### Norie

##### Well-known Member
Why do you want to look at the text for the conditional formatting?

Can't you just look at the dates?

#### EDSTAFF

##### New Member
Norie said:
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.

L.M.

#### Norie

##### Well-known Member
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.

Replies
1
Views
386
Replies
2
Views
290
Replies
3
Views
247
Replies
5
Views
315
Replies
8
Views
408

1,195,920
Messages
6,012,308
Members
441,690
Latest member
CyberWrek

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back