Conditional Formatting on cells with the result of a formula

1
votes

I have a list of domains that my company owns. In the control panel it doesn't show an expiration date but we always choose a two year option on the domain.

What I have figured out is how to calculate how many days are left till renewal. But what I can't figure out is how to apply a 3 color coded conditional formatting on the cells that return the number days till renewal.

Example:

Here is the formula to find the number of days left.

=DATEVALUE("16-July-2016")-TODAY()&""

So I am trying to get numbers from:

  • 0-100 a red color
  • 101-400 a yellow color
  • 401-730 a green color

I keep trying to apply it to the cells with that show the days left, but it won't show any color.

Can anyone help?

3
excelexcel-formulaconditional-formatting
Depending on the version of excel you are using, you should be able to just right click the cell(s) you want and select "conditional formatting". Set a rule for each condition you want. wikihow.com/Apply-Conditional-Formatting-in-Excel – PWilliams0530
I was said that this is not what OP asked ... – tagoma

3 Answers

2
votes

With &"" at the end of your formula, the result outputs as TEXT, and Conditional Formatting is trying to evaluate numbers. Set your formula to just =DATEVALUE("16-July-2016")-TODAY() and see if that fixes it.

0
votes

Maybe three separate rules, applied to whichever column your values (excluding &"") are in (here assumed to be A):

SO24873783 example

-1
votes

In Excel, Home -> Conditional Formatting. See this introduction on Conditional Formatting.