Second of Two Conditional Formats Not Functioning??

  • Thread starter Thread starter GBL
  • Start date Start date
G

GBL

Hi:
I've got two columns:
1st column of prescription numbers (D17:D90) (formatted as numbers).
2nd column of dates (L17:L90) (formatted as dates).

I've chosen to use conditional formatting for accomplishing the
following:
Condition #1. Mark duplicates of the prescription numbers with:
=COUNTIF($D$17:D$90,D17)>1
Condition #2. Strikethrough selected duplicates with:
=DATEDIF(L17,TODAY(),"d")>0

Condition #1 works fine but condition #2 shows no effect.
Any ideas???
Thank you in advance!!
 
Hi

i think the reason it may not be working is that you have both conditions on
the same range ... basically (as i understand it anyway) when excel looks at
conditional formatting it looks at the first condition and if it returns
true, it applies the formatting and then stops looking. If however, the
first condition returns false, excel then looks at the second condition etc

it seems to me that you're wanting excel to evaluate the second condition
when the first returns true - if this is the case then i would suggest
combining the conditions e.g.
=AND(COUNTIF($D$17:D$90,D17)>1,DATEDIF(L17,TODAY(),"d")>0)
and then applying the appropriate formatting

Cheers
JulieD
 
Hi
what do you mean with 'does not work'
For it self this function would work. Maybe you have to combine your
conditions into one and choose a combined format. e.g.
=AND(DATEDIF(L17,TODAY(),"d")>0,COUNTIF($D$17:D$90,D17)>1)
and choose your format together with a strike through
 
only the first CF that evaluates as true will execute.

If the cells that evaluate true in your second CF will also evalute tru
in your first CF, make your first CF

=AND(COUNTIF($D$17:D$90,D17)>1, DATEDIF(L17,TODAY(),"d")>0)

then put your CF1 as CF2
 
Hi:
Thought this may be of interest to you. It took me several days to find
the reference; but found it in John Walkenbach's MS Excel 2000 Formulas book
(Page 461) where he discusses multiple conditions using conditional
formatting:

"If none of the specified conditions is TRUE, the cells keep their existing
formats. If you specify multiple conditions and more than one condition is
TRUE, Excel applies only the first TRUE condition. For example, you may
specify the following two conditions:
Cell Value is between 1 and 12
Cell Value is less than 6
Entering a value of 4 satisfies both conditions. Therefore, the cell
will be formatted using the format specified for the first condition."

Many thanks to you for your help!!

Warm Regards,
GBL
 
Back
Top