HOWTO: What is the formula to have evaluate a range of value
"Fred Smith" wrote:
Do you mean between 5 and 20 days before today, or after today?
If after, use:
=and(a1<"",a1+5=today(),a1+20<=today())
If A1+5=TODAY() is true, then A1+20TODAY() is true. So
AND(A1+5=TODAY(),A1+20<=TODAY()) is always false.
Moreover, I believe A1+5=TODAY() is true when A1 is 5 days or less before,
not after, TODAY().
I presume you meant to write AND(A1<"",TODAY()<=A1-5,A1-20<=TODAY()), which
is true if the date in A1 is between 5 and 20 days after today.
But the condition A1<"" is superfluous for a "Formula Is" conditional
format. It is useless in a normal formula because if A1="", the expressions
A1+5 and A1+20 cause a #VALUE error.
It would be more correct to write:
AND(A1<"",TODAY()+5<=A1,A1<=TODAY()+20)
But the condition A1<"" is still superfluous for a normal formula as well
as for a "Formula Is" conditional format. Since text is always considered
greater than a numeric value, it is sufficient to write:
AND(TODAY()+5<=A1,A1<=TODAY()+20)
That is false when A1="" because A1<=TODAY()+20 is false.
----- original message -----
"Fred Smith" wrote in message
...
Do you mean between 5 and 20 days before today, or after today? If after,
use:
=and(a1<"",a1+5=today(),a1+20<=today())
Regards
Fred
"apache007" wrote in message
...
Hi David,
The thing is that Criteria 2 must be a formula, because I want to
evaluate a
DATE condition that meet value between 5 and 20.
On my cell: I want to caculate If
8 Jun 2010 - Today () is it between 5 and 20.
If so, Formatting applied.
"David-Melbourne-Austraalia" wrote:
Hi Apache007
If I've understood correctly, you want to highlight cells in A1:A100
which
are not empty and contain a value between 5 and 20 using Conditional
Formatting.
To achieve this, you only need one criteria - Go into Conditional
Formatting
and set the following:
Condition 1
Cell Value Is between 5 and 20
and then obviously set the highlighting you want under the Format
button.
You don't need to check for non-blank cells as a blank cell simply fails
to
meet the requirement of being between 5 and 20.
Hope this helps.
"apache007" wrote:
Hi All,
Let say I have data Coloum A1:A100 containes number data
and I want to HIGHLIGHT using Conditional Formatting, those cells
that have
the following criteria:
1. Is not empty
2. Has a value between 5 and 20.
I don't know the formula for Criteria #2.
Thanks.
|