Conditional Formatting using Formulas and WEEKNUM

[Kirk]

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().

 

[Sloth]

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).

 

[Sloth]

=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).

 

[Kirk]

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

 

[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

 

[Pecoflyer]

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

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

--
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

 

[Phippsy]

Brilliant thanks

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