In Google Sheets, if we need to know what specific week a date falls on, we can make use of the WEEKNUM formula to find that out. The week numbers will range from 1 to 53.

Syntax

WEEKNUM(date, [type])

  • date – is the input date that the formula uses to determine the week of the year. This can be a reference to a cell that holds a date, or a formula that returns a date. Not only that, it can even be a number that the Google Sheets can evaluate to a date value.
  • type – this is an optional indicator that lets the formula know where to begin the week from. As per the official documentation, the valid inputs for this parameter are 1, 2, 11 through to 17 and 21. Also, there are two different week numbering systems the formula uses to return the output.
    • System 1: The week that has January 1, is week # 1. All the types except 21 use this system.
    • System 2: The week that consists of the year’s first Thursday are week # 1.Type 21 uses this system, which often goes by the name “European system for numbering weeks”.

Now that we have seen what each system signifies, let us also understand what each WEEKNUM formula type stands for. Please consider the table below. It shows what each type represents in terms of the week’s starting day and the week’s ending day.

WEEKNUM Formula - Illustration 2

Usage: WEEKNUM Formula

Let us dive into the practical application of the formula so that we get a clearer picture of its working. Please see the snapshot below. We have listed a series of dates column A. And, the subsequent columns contain the WEEKNUM formulas applied for each available type of those dates.

WEEKNUM Formula - Illustration 1

Let us pick the type 11 and understand how the formula works. Since this type uses System 1 to calculate week numbers, it considers the week with January 1 as week # 1. And, according to the previous table, the week starts on Monday and ends on Sunday for this type. Therefore, week # 1 spans from January 1, Friday to January 3, Sunday. Week # 2 starts from January 4, as it is Monday, and ends on January 10, Sunday.

Now, we will try and understand the case of type 21. This uses System 2, that assumes a week with year’s first Thursday as week # 1. Taking a quick glance at the data, we know that the first Thursday of the year falls on January 8. And, going by the first snapshot, the week starts on Monday and ends on Sunday for this type as well. So, the week # 1 starts from January 4, Monday and ends on January 10, Sunday. What about the initial 3 days that are not part of week # 1? The WEEKNUM formula assigns it the previous year’s last week number, which is 53 in this case.

 

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>