Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Conditional formatting - weeknum (97)

    I am trying to compare weeks in conditional formatting and the formula I am using is: =WEEKNUM(B61)=WEEKNUM(TODAY()).

    When I do this, Excel generates an error that says "You may not use references to other worksheets or workbooks for Conditional Formatting criteria." I am certainly not doing that so the error must have something to do with using a function from the Analysis ToolPak.

    Is there a work around for WEEKNUM?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional formatting - weeknum (97)

    You can enter the formula =WEEKNUM(B61)=WEEKNUM(TODAY()) in a cell, and refer to that cell in the Conditional Formatting dialog.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Conditional formatting - weeknum (97)

    Another option that does not require an itermediate calculation in the sheet is a named formula.

    Select A1
    insert - name - define
    Names in workbook (change as desired):
    <pre>WeekNumIsToday</pre>

    Refers to (note that the A1 is relative/relative):
    <pre>=WEEKNUM(A1)=WEEKNUM(TODAY())</pre>

    [ok]
    Now select your range of cells and use as conditional formatting formula (or whatever you named the formula):
    <pre>=WeekNumIsToday</pre>


    Steve

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Conditional formatting - weeknum (97)

    Hi Don,

    You can simulate the WEEKNUM function with:
    =ROUNDUP(REF1/7,0)-INT(DATE(YEAR(REF),1,0)/7)
    where 'REF' is the cell or value you're testing. This avoids the need to use the Analysis ToolPack - which is a good thing if the worksheet is liable to be used by someone who may not have it installed.

    For your conditional formatting task, this becomes:
    =ROUNDUP(B611/7,0)-INT(DATE(YEAR(B61),1,0)/7)=ROUNDUP(TODAY()/7,0)-INT(DATE(YEAR(TODAY()),1,0)/7)
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •