Why Weeknum Function Returns Wrong Value ?
This year WEEKNUM Function returns wrong value if you are using it as usual. Because Excel considers the week containing 1st of January as week 1 in this scenario. We can overcome this issue by using return type systems.
Syntax for WEEKNUM Functions is as below:
We usually use WEEKNUM Function with return type as 1 or 2 based on our calendar system. But there is more to know about return type. There is 2 systems for rerun type:
- System 1 considers the week containin 1st of January as first week of the year. If you choose to use system 1 return types, you need to input retun type as 11 instead of 1 or 12 istead of 2.
- System 2 considers the week containing first Thursay of the year as first week of the year. If you choose to use system 2 return types, you need to input retun type as 21 instead of 2.
Here is how it works:
|First day of he year||01/01/2020||01/01/2021|
|Week number on calendar||22||21|
You can adjust your WEEKNUM formula based on above instruction to get correct results. All you need to do is to be aware of which day is the first day of the year.