Conditional Formatting Problem

[vtgannon]

In the attached example spreadsheet in Cell H123 I have applied the following conditional formatting:
0yrs/0mos/0day and 0yrs/2mos/29day will be Red
0yrs/2mos/30day and 0yrs/5mos/29day will be Yellow
0yrs/5mos/30day and 1yrs/6mos/0day will be Green

But for some reason it is staying red, even though the time remaining shows 0yrs/10mos/4day, not sure where I am going wrong here, anyone have any ideas.

[Ace_XL]

Your data is actually text whereby Excel reads 0yrs/10mos/4day as lesser than 0yrs/2mos/4day

Use numbers and change the CF conditions accordingly

[LG99]

I haven't looked at your spreadsheet, yet, but the most common reason why this may be happening is that the box "stop if true" is checked in the conditional formatting rules manager.

[6StringJazzer]

Your formula generates a string, not a number. And your conditional formatting rules are using "greater than" to compare strings, not numbers. The string "0yrs/10mos/4day" is LESS THAN the string "0yrs/2mos/29day", because strings are compared using alphabetical order.

Your whole approach can be greatly simplified if you can change your intervals to a number of days (for example, under 90 is red, 90 to under 180 is yellow, 180 and up is green). Then instead of building this string with DATEDIF, you can do a straight subtraction of the dates to get the number of days.

[vtgannon]

Ace, I have it formatted as date. LG I tried unchecking the stop if true, it did not make a difference.

[vtgannon]

6string so basically rewrite my formulas, do you have an example formula I could use as a basis?

[6StringJazzer]

Ace, I have it formatted as date. LG I tried unchecking the stop if true, it did not make a difference.

You do not have column H formatted as date, which the source of the problem. (LG's advice was given without looking at your file and does not apply to your case.)

Here is a revision of your file as an example. For clarity, I had to remove overlapping and conflicting CF rules, but you can use this as an example. Your date strings are now a number of days instead of a string with "xyrs/ymos/zday". CF rules now look at number of days.

[vtgannon]

6String that works very much appreciated.

[6StringJazzer]

Glad to help. Thanks for the rep, and thanks for marking your thread Solved!