Forum Discussion
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
- 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).
- 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.- Thanks Mr. Norman_Harker, your answer is very satisfied and logical, you have clear my doubt,
many many thanks to you.
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 W1All 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.
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.
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?
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 begins1 Week begins on Sunday. Weekdays are numbered 1 through 7. 2 Week 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.
- 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
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)
