WEEKNUM Function – Google Sheets

The function help text for the WEEKDAY function

The WEEKNUM function accepts a date and returns the week number (1 through 54) of that date.

The date must be in a valid format, so you have to use the DATE function, wrap it in quotes, or use a cell reference to a date. If your days, months, and years are in different cells, use the DATE function to combine them.

Syntax

=WEEKNUM(date,[type])

  • date – The date for which you want the week number. This input is required.
  • [type] – Optional specification of the first day of the week and which week is the year’s first week.
    • The year starts with the week containing January 1.
      • 1 (or not specified) – The week begins on Sunday
      • 2  – The week starts on Monday
      • 11 – The week starts on Monday (same as #2)
      • 12 – The week starts on Tuesday
      • 13 – The week starts on Wednesday
      • 14 – The week starts on Thursday
      • 15 – The week starts on Friday
      • 16 – The week starts on Saturday
      • 17 – The week starts on Sunday
    • The year starts with the first week containing a Thursday.
      • 21 – The week starts on Monday
      • This is the same as the ISOWEEKNUM function.

Video Tutorial

Related Functions

DATE – Takes separate year, month, and day values and returns them as a date

DATEVALUE – Returns the serial value of a date

ISOWEEKNUM – Returns the ISO day of the week. Similar to WEEKNUM.

Errors

#NUM – The inputs are numbers but are not valid dates for the date input, or the type input is out of range. This could happen if you use a negative number for the date or a number for the type that is not one of the preset options (i.e., 1, 2, 11).

#VALUE! – The inputs are something that doesn’t convert to a number such as “The other day” or “Yester-yester-day.”

Examples of the WEEKNUM Function

Example 1 – Simple Usage

Let’s take a look at using the function with nothing specified for the type parameter.

Simple examples of the WEEKNUM function
Simple Usage of WEEKNUM

In cells C2 and C3, the function uses relative cell references to bring in the date values. 3/4/16 is the 10th day of the week in 2016 using the default week counting type and 3/21/17 is the 12th week. You can also type dates directly into WEEKNUM, like we did in cell C4, as long as you surround them with quotes like "3/21/17".

The third way to input dates is by using the DATE function. Let’s look at the now.

Example 2 – WEEKNUM Combined with the DATE Function

You may have date values in different cells.

Using WEEKNUM and DATE together
Using the DATE Function with WEEKNUM

The DATE function provides a reliable method to produce valid dates from three numbers. In this example, November 7, 2019’s week number is 45 and the week number for May 25, 2021 is 22.

We have been not been specifying a type number yet. Let’s look at that next.

Example 3 – Comparing the Types of Week Counting

Following is a chart comparing the different WEEKNUM types and their behavior near the beginning and end of a year.

A table comparing the different WEEKNUM types
Comparison of Week Counting Types

The transition between 2025 and 2026 is a good example of the different types because the first Monday and Thursday in 2026 are in different weeks.

Types 117 change the week number to 1 on January 1. This can result in less than seven days in the first and last week of a year.

However, type 21 uses the ISO standard and therefore can start week 1 in the previous year and always has seven days in a week.

Live Examples in Sheets

Go to this spreadsheet for the examples of the WEEKNUM function shown above that you can study and use anywhere you would like.

2 thoughts on “WEEKNUM Function – Google Sheets”

  1. What is the required input for an entire column using the same function, e.g. all of column B is a date in each cell, so all of column A is a corresponding week number and only requires typing ‘=’ so it’s autofilled with the funtion and just ‘Enter’ confirms it…?
    I had this set up, but it no longer works & I cannot find a single reference to it on the internet despite having looked it up in the first place.

    Reply

Leave a Comment