Forum Discussion

Prem_Kant_Pandey's avatar
Prem_Kant_Pandey
Copper Contributor
Mar 11, 2021
Solved

Weeknum Function

While we using WEEKNUM Function the return type showing as 1 and 17 where days begins on Sunday and I search there is no difference between both returns type same is happing with 2 and 11 in case days begins on Monday.

So is there any specific reason that given two return type for single day?

  • You ask :
    "So is there any specific reason that given two return type for single day?"
    It's historic.
    The answer is that WEEKNUM was part of the original Analysis ToolPak set of functions used by Microsoft in the first Windows version of Excel.
    That function only had 2 return_types.
    In Excel 2007 Analysis ToolPak functions became installed as 'normal' functions rather than requiring the installation of the Analysis ToolPak AddIn.
    In Excel 2010, Excel added the return_types 11 to 17 plus return type 21.
    You are correct that:
    the algorithm for return_type 1 = same as return_type 17
    the algorithm for return_type 2 = same as return_type 11
    If look at the table I provided earlier you'll see on vertical scanning of 11 through to 17 the neat sequence: Mon, Tue, Wed, Thu, Fri, Sat, Sun
    If Microsoft had decided that existing options 1 and 2 made options 11 and 17 otiose, then I think that users would have been more confused than by the duplication.
    As usual, Microsoft could not have removed Options 1 and 2 because existing workbooks used those options.
    Hope that helps more than it confuses.


11 Replies

  • Norman_Harker's avatar
    Norman_Harker
    Brass Contributor
    That's OK. You've prompted me to think about updating the week numbering Callendar that Ron de Bruin (MVP) and I wrote many years ago and which is still a popular upload from his site. At present, we only have Options 1, 2, ISO, and simplistic (D1 W1 is 1st Jan).
  • Norman_Harker's avatar
    Norman_Harker
    Brass Contributor
    You ask :
    "So is there any specific reason that given two return type for single day?"
    It's historic.
    The answer is that WEEKNUM was part of the original Analysis ToolPak set of functions used by Microsoft in the first Windows version of Excel.
    That function only had 2 return_types.
    In Excel 2007 Analysis ToolPak functions became installed as 'normal' functions rather than requiring the installation of the Analysis ToolPak AddIn.
    In Excel 2010, Excel added the return_types 11 to 17 plus return type 21.
    You are correct that:
    the algorithm for return_type 1 = same as return_type 17
    the algorithm for return_type 2 = same as return_type 11
    If look at the table I provided earlier you'll see on vertical scanning of 11 through to 17 the neat sequence: Mon, Tue, Wed, Thu, Fri, Sat, Sun
    If Microsoft had decided that existing options 1 and 2 made options 11 and 17 otiose, then I think that users would have been more confused than by the duplication.
    As usual, Microsoft could not have removed Options 1 and 2 because existing workbooks used those options.
    Hope that helps more than it confuses.


    • Prem_Kant_Pandey's avatar
      Prem_Kant_Pandey
      Copper Contributor
      Thanks Mr. Norman_Harker, your answer is very satisfied and logical, you have clear my doubt,

      many many thanks to you.
  • Norman_Harker's avatar
    Norman_Harker
    Brass Contributor

    Prem_Kant_Pandey 

    I think you'll find the following description of the various returns for return_type is correct. I've tested it anyway:

     

    return_type is:

    A number that determines the type of return value:

    1 = Default = 1st Sun on or after Jan 1 is D1 W1
    2 = 1st Mon on or after Jan 1 is D1 W1
    11 = 1st Mon on or after Jan 1 is D1 W1
    12 = 1st Tue on or after Jan 1 is D1 W1
    13 = 1st Wed on or after 1 Jan is D1 W1
    14 = 1st Thu on or after 1 Jan is D1 W1
    15 = 1st Fri on or after 1 Jan is D1 W1
    16 = 1st Sat on or after 1 Jan is D1 W1
    17 = 1st Sun on or after 1 Jan is D1 W1
    21 = Mon of week with first Thu is D1 W1

     

    All except return-type 21 start the week on the first return_type based day that falls on or after January 1st in the year of the serial number.

    Option 21 is the ISO8601 system which provides for Day 1 of Week 1 to be the Monday of the first week that has a Thursday. Alternatively, and having the same effect, it is the Monday of the week in which Jan 4th falls.

    Options 11 to 21 were introduced in Excel 2010.

     

    Hope this helps more than it confuses.

     

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor

    Prem_Kant_Pandey 

    That's historical reason. Numbers from 11 to 17 were introduced in Excel 2010 and they are not compatible with previous version of Excel. However, to keep compatibility from oldest versions to new ones, numbers before 10 which are used in previous versions, are also kept in the list.

     

    These days very few people are on pre-Excel 2010 versions, however they are, thus both sets are kept.

    • Prem_Kant_Pandey's avatar
      Prem_Kant_Pandey
      Copper Contributor

      SergeiBaklan 

      So as you saying that, there is no difference between return type 1 and 17 or 2 and 11 in WEEKNUM Function. am I right?

  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor

    Prem_Kant_Pandey 

    Return value

    The WeekNum function considers the week containing January 1 to be the first week of the year. However, there is a European standard that defines the first week as the one with the majority of days (four or more) falling in the new year. This means that for years in which there are three days or less in the first week of January, the WeekNum function returns week numbers that are incorrect according to the European standard.

     

    The following table represents possible values for Arg2.

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

     

    I would be happy to know if I could help.

     

    Nikolino

    I know I don't know anything (Socrates)

     

    * Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.

    • Prem_Kant_Pandey's avatar
      Prem_Kant_Pandey
      Copper Contributor
      It's not that's I ask, I want to know why 1 and 17 for Sunday, we can use 1 only or 17 only
      • NikolinoDE's avatar
        NikolinoDE
        Platinum Contributor

        Prem_Kant_Pandey 

        The WEEKNUM function can be used in two systems.

        System 1 The function counts the start of the week on January 1st, ie January 1st of each year is the first week.

        System 2 The week for function counting starts on the first Thursday of the year.

        For example, let's say the 1 is not the first week because it is Wednesday.

        However, 1/2020/1 is the first week as it is the first Thursday of year 2.

         

        Return_type

        Week begins with

        System

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

         

        Return value

        The WEEKNUM function returns the week number (1-54).

         

        Remarks

        With a series date, Excel stores data internally and gives the number of days since January 1, 1900. For example, January 1, 1900 is serial number 1, January 1, 2020 is serial number 43831, because it was 43831 days after January 1, 1900.

         

        error

        If the serial_num argument is outside the range for the current date base value, an #NUM! Returned. For example, if the serial number (-3) is a negative value or a large number (100000000), an error value is returned.

         

        If the return_type is outside the range given in the table above, the #NUM! Returned.

         

        I hope I was able to answer your question.

         

        Thank you for your understanding and patience

         

        Nikolino

        I know I don't know anything (Socrates)

Resources