WEEKNUM Function – Google Sheets

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 cell containing 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 which day is the first day of the week and which week is the year’s first week.
    • 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
    • 21 – The week starts on Monday, and the week containing the first Thursday of the year is week 1. See ISOWEEKNUM for more.

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

The WEEKNUM function with different inputs and outputs
Several Examples of the WEEKNUM Function

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