Conditional format

  • Thread starter Thread starter Zvi Grinberg
  • Start date Start date
Z

Zvi Grinberg

Here's my need. next are my problems.


- I have 2 columns. One for a name, second for an expiration date
- I want to conditionally format the name according to the expiration
date and current date (highlight, if today()-365 > "expiration
date")
- I want to apply this CF to all other relevant rows.


Now here are the problems.
1. I could not find a way to copy the format to other rows. It copied
the original row number to all other rows
(i.e - if forumla condition included the exp-date in cell C2, I will
get C2 in all subsequent CF cells. I would then have to manually edit
to appropriate row - C3, C4...Cn)

2. What would be the correct formula phrasing to achieve my condition.

I was unable to use a cell cormatted as date, for my calculations. I
did a workaround by copying the exp-date to another column, added
another column with the today() date - both formatted to DATEVALUE
representation. I then added yet a new column with the difference, and
used it for the formula.

There must be a more elegant way.

Thanks a lot in advance
 
With Names in column A, ExpDates in column B, headers in row 1.

Try something like -

=DATEDIF($B2,TODAY(),"y")>0

as the Conditional Formatting for A2. You should then be able to copy this
with the Format Painter tool. It may be you have your formula "too absolute"
(eg: $B$2) so it's reffing B2 all the time, instead of the ExpDate for the
row(s) in question.

HTH,
Andy
 
Isn't this DateDif() function only available if the Analysis Toolpak Add-In
is loaded?
It is undocumented in standard Help; If user doesn't have ATP loaded,,,
there's a problem here..
Tks,
 
Isn't this DateDif() function only available if the Analysis Toolpak
Add-In
is loaded?
Click to expand...

Not according to my Help. As a test, I disabled ATP and DATEDIF still ran.
Chip's site says "You can use DATEDIF as you would any normal worksheet
function, because it is a normal worksheet function". But if you're right,
you've solved "the problem" (ie: install it).

Rgds,
Andy
 
Thanks Andy,
I just remember something odd about it; perhaps that it was not documented
by Microsoft, not like I said "only being available thru the ATP add-in"..
Appreciate the clarification..
JMay
 
I just remember something odd about it

It's only covered in Help in XL2K (AFAIK - can't vouch for 2003).

Best rgds,
Andy
 
Back
Top