Conditional formatting for cells containing formulae

[SuzieKD]

Hi all,

I'm having trouble applying some simple conditional formatting to a cell range. I want to set up a simple traffic light system for a range of dates, to show when they're close to expiry/expired. Simple enough, but the issue is that the range of cells contains a formula to return the date and this seems to be screwing up the conditional formatting rules.

The cells contain this formula; =IF(ISBLANK(G14),"",G14+90), referring to another date in cell G14.

Is there a way to set the conditional format rule to read against the value that the formula returns, rather than reading the formula itself?

At the moment, when I set up the rule to highlight red against ='<TODAY()', it just highlights the whole range, regardless of the date shown in the cell.

Any wisdom much appreciated!

 

[SuzieKD]

Not sure why, but the above post cut off my last sentence, won't allow me to edit it, and also cuts it off at the same point when I re-type it here! Very weird.

Basically, I set the rule to highlight red any dates in the past, using the TODAY function, but it ends up highlighting the whole cell range.

 

[FDibbins]

Makes no different whether there is data or a formula in a cell, CF (and any formula for that matter) work based on what is displayed, not (necessarily) what the actual cell contents are.

Also, CF only works on TRUE/FALSE (1/0), so you just need to construct the CF rule to return 1 of those

If G14 really does contain a date, and not text looking like a date, then the CF should work (depwending on what you used)

Can you explain what you used for the CF?

 

[SuzieKD]

Thanks for this. Yes - that's what I thought, but it's stubbornly refusing to work!

Also this forum is driving me nuts...wherever I type in a formula it cuts off the rest of the line from that point onwards so you can't read my response!

So forgive me for writing this out in words but otherwise I can't respond...

At the moment I'm selecting to format based on a formula and then using 'less than TODAY brackets'. It's supposed to return a red fill but nothing happens! The cell contains the formula as quoted in my original post and returns the date in the format 15-Jun.

But for the life of me I cannot get it to bloody highlight! It's so annoying.

 

[FDibbins]

Also this forum is driving me nuts...wherever I type in a formula it cuts off the rest of the line from that point onwards so you can't read my response!

I would hazard a guess and say that you tried to type < or > ? If so, put a space around them, otherwise the forum thinks you are adding HTML, which is does not like.

Perhaps my suggestion above will help you paste the full formula now

Just because a cell shows/contains 15-Jun, does not necessarily mean that it contains a real date, it could contain text that just looks like a date.

What you need to understand about dates and times in excel is…

a date is just a number representing the number of days passed since 1/1/900...and then formatted in a way that we recognize as a date. So, for instance, today (Fri 13 Jan 2017) is actually 42748

Time is actually a decimal part of 1 (day), so 06:00 AM is 0.25, 12 noon is 0.5 and 18:00 (or 6 PM) is 0.75