The following article introduces you to the WEEKNUM function - one of the functions in the date and time group function is very popular in Excel.
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!