Conditional formatting for cells containing formulae

SuzieKD

New Member
Joined
Apr 29, 2016
Messages
11
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!
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

SuzieKD

New Member
Joined
Apr 29, 2016
Messages
11
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.
 
Last edited:

FDibbins

Well-known Member
Joined
Feb 16, 2013
Messages
6,723
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

New Member
Joined
Apr 29, 2016
Messages
11
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.
 
Last edited:

FDibbins

Well-known Member
Joined
Feb 16, 2013
Messages
6,723
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,464
Messages
5,596,288
Members
414,052
Latest member
Dual Showman

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top