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:

=WEEKNUM(serial_number,[return_type])

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:

  1. 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.
  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
Day Wednesday Friday
Example date 25/05/2020 25/05/2021
Week number on calendar 22 21
=WEEKNUM(Example_Date;2) 22 22
=WEEKNUM(Example_Date;21) 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.

 

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.