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

"Bob Phillips" wrote in message
...
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)



"Ken" wrote in message
...
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