Forum Discussion
Weeknum Function
- 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.
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.