Conditional formatting

S

Space Ape

I am trying to use following formula in conditional formatting field.

=if(g5=weeknum(today(),2),true,false)

I get an error...

But the same formula works fine when pasted in a cell.

I need to set condinal formatting according to the formaul.
I think there is someway to work around.

Could some one help me, please?
 
B

Bob Phillips

The problem appears to be that WEEKNUM is a function from the Analysis
Toolpak, and CF does not like that.

One possible workaround is to put the test g5=weeknum(today(),2) in an
adjacent cell,and test that cell in the conditional format formula. Not
good, but it works.

ALternatively, you could use some other formula. Chip Pearson has a web site
that discusses the week numbers. You may want to visit his site and see if
there is a formula there that helps.

http://cpearson.com/excel/week­num.htm


--

HTH

RP
(remove nothere from the email address if mailing direct)
 
D

David McRitchie

I didn't notice Bob Phillips already answered 6 hours earlier
but there are a few additional points thrown in here, and though
I did test the formula directly in a cell, I failed to mention that
you should do that, which was
one of Bob's points about testing formula directly in a cell.

Rather than beating our heads against the wall
or trying to duplicate your example to see what we
get please don't just say I got an error,
please type the specific error that you got (word for word)
for at least the first meaningful line. So we can search Google
Groups to find an answer for you, even though you searched
every where, right?
http://www.mvps.org/dmcritchie/excel/xlnews.htm

If I turn off Analysis ToolPak under Tools, Addin
I get a #VALUE! error [hit OK if turning back on]

If you look in HELP for # or specifically #VALUE!
What does the error #VALUE! mean?
you will get some pretty good hints. Like no such function.

WEEKNUM is
If this function is not available, run the Setup program to install the Analysis ToolPak. After you install the Analysis ToolPak,
you must enable it by using the Add-Ins command on the Tools menu.

There are different ways of calculating the WEEKNUM so if either of
the methods in your Excel are not what you want then also see
Week numbers in Excel
http://www.cpearson.com/excel/weeknum.htm
which also include ISO Week Number.
I don't know which method the English postal services use, I think
they use something different or I'm thinking of something else they
do differently.

For most questions about Conditional Formatting, see
http://www.mvps.org/dmcritchie/excel/condfmt.htm
I also started one on Errors, guess I can start updating it.
http://www.mvps.org/dmcritchie/excel/errors.htm
 
Top