Weeknum vs Conditional Formatting

[Guest]

Excel2003 ... Weeknum vs Conditional Formatting???

Cell E1 ... =IF(ISBLANK($D1),"",D1+7) ... Return = 01/14/07 ... ok
Cell E3 ... Conditional Format Formula ... Format Pattern Gold ... not
working???

=weeknum(today(),2)<>weeknum(e$1,2) ... Returns error message =

"You may not use references to other worksheets or workbooks for Conditional
Formatting criteria."

I am stuck ... Help! ... Thanks ... Kha

 

[Bob Phillips]

The Weeknum function is part of the Analysis Toolpak, an add-in, which Excel
sees as another workbook. Try this instead

=1+INT((TODAY()-(DATE(YEAR(TODAY()),1,2)-WEEKDAY(DATE(YEAR(TODAY()),1,1))))/7)<>1+INT((TODAY()-(DATE(YEAR($E$1),1,2)-WEEKDAY(DATE(YEAR($E$1),1,1))))/7)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[T. Valko]

=weeknum(today(),2)

You could give that portion a defined name like, say, WeekToday

Then:

=WeekToday<>1+INT((TODAY()-(DATE(YEAR($E$1),1,2)-WEEKDAY(DATE(YEAR($E$1),1,1))))/7)

Biff

 

[T. Valko]

I think the second half of the formula is mixed up. You need to replace
TODAY() with E1.

With that change some of the week numbers are off by 1 compared to Excel's
WEEKNUM function: =WEEKNUM(date,2)

Your formula seems to be based on return_type 1 (Sunday is first DOW)

In my other reply I assumed that was correct and just copied that portion
into my formula.

With this portion given the defined name of WeekToday:

=WEEKNUM(TODAY(),2)

This corrected formula should work:

=WeekToday<>INT((WEEKDAY(DATE(YEAR(E1),1,2-2))+E1-DATE(YEAR(E1),1,-5))/7)

From Mike Holland posted on Dick's Daily Dose of Excel:

Replacement for the ATP WEEKNUM function:

=INT((WEEKDAY(DATE(YEAR(date),1,2-return_type))+date-DATE(YEAR(date),1,-5))/7)

Biff

 

[Guest]

The replacement for the ATP Weeknum function worked (nice to know) ... That
said ... this turned out much more complicated than I anticipated ... but
"thanks" to you & others that continue to support these boards ... I am there
.... Thanks ... Kha

 

[T. Valko]

You're welcome. Thanks for the feedback!

Biff

The replacement for the ATP Weeknum function worked (nice to know) ...
That
said ... this turned out much more complicated than I anticipated ... but
"thanks" to you & others that continue to support these boards ... I am
there
... Thanks ... Kha