How to Use the WEEKNUM Function in Excel

This guide explains how to use WEEKNUM Function in Excel, including its syntax, required arguments, practical examples, and common errors.

Description: The function returns the ordinal number of the week of the year for the particular day within that week. There are 2 systems used in the function to determine the week number:

- System 1: The week with January 1 is the first week of the year and is numbered 1.

- System 2: The first Thursday of the year is the first week and is numbered 1. This system is called the methodology defined in ISO 8601 (European numbered week).

Syntax: WEEKNUM (serial_number, [return_type])

Inside:

- serial_number: The value of the day of the week to calculate the order of the week, is a required parameter.

- return_type: The return value of the function, is an optional parameter including the following values:

+ return_type = 1 or skip -> week starting on Sunday and numbered 1.

+ return_type = 2 -> week starts on Monday and is numbered 1.

+ return_type = 11 -> week starting on Monday and numbered 1.

+ return_type = 12 -> week starting on the 3rd and numbered 1.

+ return_type = 13 -> week starts on Wednesday and is numbered 1.

+ return_type = 14 -> week starts on the 5th and is numbered 1.

+ return_type = 15 -> week starts on Friday and is numbered 1.

+ return_type = 16 -> week starts on Saturday and is numbered 1.

+ return_type = 17 -> The week starting on Sunday is numbered 1.

+ return_type = 21 -> week starts on Monday and is numbered 2.

Attention:

- Excel stores dates as sequential serial numbers to facilitate the calculation process.

- If the Serial_number value is outside the basic range of the date value -> the function returns the #NUM! Error value

- If the return_type value is not in its values as above -> the function returns the #NUM! Error value

For example:

Calculate the ordinal number of the week in the year containing the following dates:

- In the cell to calculate enter the formula: = WEEKNUM (E7, F7).

- Press Enter -> 1/1/2016 at the 1st week of the year:

- Similarly copying the formula for the remaining values results:

- Case:

+ The serial_number value is an invalid date -> the function returns the error value #VALUE!

+ The return_type value is outside the range of values -> the function returns the #NUM! Error value

Above are instructions and some specific examples when working with WEEKNUM function in Excel.

Good luck!