Results 1 to 3 of 3

Thread: Weeknum issue

  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    Chantilly, VA USA
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I need to sort/subtotal/analyze data based on week of the year. I'm familiar with the WEEKNUM function...but I need to sort etc by a Work Week that begins on Saturday. The options for WEEKNUM seem to be a week that begins on Sunday (1) or Monday (2). Can anyone offer some help on how to sort etc by a Work Week that begins on Saturday?

    Using an IF Statement seems to get the result I want:
    =IF(WEEKDAY(A1)=7,(WEEKNUM(A1)+1),WEEKNUM(A1))

    but was hoping to go through fewer wickets.

    thanks

    Don

  2. #2
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Hello - Can you use this?
    WEEKNUM(A1,7)

    If that does not work, can you attach a sample?

    Tim

    FYI ......

    Syntax

    WEEKNUM(serial_num,return_type)

    Serial_num is a date within the week. Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text.

    Return_type is a number that determines on which day the week begins. The default is 1.

    Return_type Week Begins
    1 Week begins on Sunday. Weekdays are numbered 1 through 7.
    2 Week begins on Monday. Weekdays are numbered 1 through 7.

  3. #3
    New Lounger
    Join Date
    Jun 2010
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Don,

    Not sure if this works in previous versions, but the description below is from the Help file in Excel 2010. You should be able to use WEEKNUM(A1, 16) to get the weeks starting on a Saturday.

    Hope this helps.

    ========

    Description
    Returns the week number of a specific date. For example, the week containing January 1 is the first week of the year, and is numbered week 1.

    There are two systems used for this function:

    System 1 The week containing January 1 is the first week of the year, and is numbered week 1.
    System 2 The week containing the first Thursday of the year is the first week of the year, and is numbered as week 1.

    Syntax
    WEEKNUM(serial_number,[return_type])
    The WEEKNUM function syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):

    Serial_number Required. A date within the week. Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text.

    Return_type Optional. A number that determines on which day the week begins. The default is 1.

    Return_type Week begins on System
    1 or omitted Sunday 1
    2 Monday 1
    11 Monday 1
    12 Tuesday 1
    13 Wednesday 1
    14 Thursday 1
    15 Friday 1
    16 Saturday 1
    17 Sunday 1
    21 Monday 2

Posting Permissions

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