Mar 11 2021 10:38 AM
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?
Mar 11 2021 10:49 AM
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.
1 | 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.
Mar 11 2021 10:55 AM
Mar 11 2021 11:07 AM
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)
Mar 11 2021 11:14 AM
Mar 11 2021 11:37 AM - edited Mar 11 2021 11:38 AM
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). |
Mar 11 2021 12:25 PM
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.
Mar 11 2021 08:15 PM
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?
Mar 11 2021 10:27 PM
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.
Mar 11 2021 10:50 PM
SolutionMar 11 2021 10:54 PM
Mar 11 2021 11:09 PM
Mar 11 2021 10:50 PM
Solution