Forum Discussion
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 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.
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).