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.
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)
If I want start day of week should be on sunday we can put the return type 1 that is bydefult, but why number 17 is listed in return type although it's return sunday as start day of a week
One last tryhope I was understandable now.
Return_type Optional. A number that determines the type of return value.
Return_type Number returned
1 or omitted
Numbers 1 (Sunday) through 7 (Saturday). Behaves like previous versions of Microsoft Excel.
2
Numbers 1 (Monday) through 7 (Sunday).
3
Numbers 0 (Monday) through 6 (Sunday).
11
Numbers 1 (Monday) through 7 (Sunday).
12
Numbers 1 (Tuesday) through 7 (Monday).
13
Numbers 1 (Wednesday) through 7 (Tuesday).
14
Numbers 1 (Thursday) through 7 (Wednesday).
15
Numbers 1 (Friday) through 7 (Thursday).
16
Numbers 1 (Saturday) through 7 (Friday).
17
Numbers 1 (Sunday) through 7 (Saturday).