Conditional formatting - How do I make one rule overright another

Excel 2010.

Rule 1:
If cell range blank: format yellow fill, red border. (In this case, if it has text, then it will just be unformatted).

Rule 2:
If cell in cell range contains text "14" (it is of the date 2014, e.g. 01-Apr-14): format red fill, yellow border

So I know that rules above others in conditional formatting manager should overrule the below ones, and that clashes such as similar formatting don't work.

However I tried all kinds of formatting options, and if I put in the text I want "01-Apr-14", it just unformats the cell as it would normally.

The rule text says, Specific text - containing - ="14"

I'm wondering if it's maybe the text I'm making it look for.

Something else I'm missing?

Thanks

Originally Posted by judgeh59

a couple of questions....are you entering the 01-APR-14 in the cell?....because if you just do that then XL will consider that a date, not text...so, your CF will not "see" the 14 because it's really 41730, an XL date....okay, just one question apparently....

Yes I think that's probably the case.

I enter 1/4 and the date format sets it to '01-Apr-14' - so it probably actually is the XL date.

So in this case, is it doable? Because I do want that date format to be displayed.

test chart.xlsx

I tried using the same idea here..with no luck

Excellent, that's done it

Thanks a lot guys