Conditional Formatting using Formulas and WEEKNUM

G

Guest

I'm trying to conditional format to highlight a date cell if its in the
current week.

I know whow to use WEEKNUM to check a week's number (1-53). I did check to
make sure the Analyst Pack was loaded.

The challenge I have is getting conditional formatting using formulas to
check the current cell

I'm trying the following formula: (In Conditional Formatting)
="WEEKNUM($C$4,1) = WEEKNUM($A$1,1)" with no results
C4 is the current cell, A1 is a refernce cell with TODAY().
 
G

Guest

Remove the quotes and it should work. Quotes usually appear if you forget
the first '='. I am not sure why, they just mess things up.

I don't use the analysis toolpak, so I can't tell if the problem is with the
WEEKNUM function (I doubt it is though).
 
G

Guest

=A2-WEEKDAY(A2)=TODAY()-WEEKDAY(TODAY())

Might be more usefull, because it only includes days of the current week in
the current year. For instance 1/1/2003 and 1/1/2005 both return a WEEKNUM
of 1 don't they? With my function though, it isn't in the same week. Or is
that what you wanted? The function above assumes a week starts on Sunday.

Depending on what you want, this might not be as usefull because 12/31/2004
(a friday) would be included in the same week as 1/1/2005 (a saturday).
 
G

Guest

Thanks. This wasn't the problem. I'm thinking WEEKNUM won't work in
Conditional Formatting. If someone can help there. (I may need to set up
dummy cells with the WEEKNUM values and compare them to each other.

Kirk
 
G

Guest

PERFECT !!!! Thank you so much! Works perfectly!
And I didn't even think of what year the week is in. My conditional
formatting function was to grey out dates that were past, highlight a cell in
red that's today, and higlight a cell in yellow for what dates are this week!
I got the first two, but coouldn't figure out the 3rd.

V/R, Kirk
 
P

Phippsy

Hi

this is what i also want but I want it to shade the whole row. Does anyone
know how to do that?
Thanks
Debbie
 
P

Phippsy

Brilliant thanks

Pecoflyer said:
1. Select the cells to be formatted (say row A in this example)
2. Choose Format>Conditional Formatting...
3. From the first drop-down list, choose Formula Is
4. In the text box, enter a formula that refers to the active cell in
the selection.
For example: =$A1>75
5. Click the Format button.
6. Select the formatting options, click OK
7. Click OK
Make sure the colmun refence is preceded with the $ sign




--
Pecoflyer

Cheers -
*'Membership is free' (http://www.thecodecage.com)* & allows file
upload ->faster and better answers

*Adding your XL version* to your post helps finding solution faster
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top