The WEEKNUM Function in Excel will return the week number for a given input date.
WEEKNUM Function: A Brief
The WEEKNUM function in Excel is used to return the week number from a given input date. If the input is not a valid Excel date, the function will return a #VALUE! error. It will also return a #NUM! error if the return_type argument is not supported. The return_type must be 1, 2, or between 11 and 17.
|
Objective |
Value Returned by the function |
|---|---|
|
Aim to return the week number |
The WEEKNUM function in Excel will return the relative week number to the given input date. |
WEEKNUM Function: A Syntax
=WEEKNUM(serial_number, [return_type])
- serial_number: The date you want to find the week number for.
- return_type: A number that determines which day the week starts on. This is an optional one. By default, Excel assumes Sunday as value 1.
The following is differences between the WEEKNUM and ISOWEEKNUM functions is,
|
WEEKNUM Function |
ISOWEEKNUM Function |
|---|---|
|
Can start on Sunday (default) or Monday (based on return type) |
Always starts on Monday |
|
Week 1 is the week containing January 1st |
Week 1 is the first week with at least 4 days (ISO 8601 rule) |
|
Varies depending on chosen return type (1 or 2) |
Follows the international ISO 8601 standard consistently |
Basic Example of Using WEEKNUM Function:
The example covers the variable scenarios of using the WEEKNUM function in practical days.
In the above example, the first five input dates are valid Excel dates, so the WEEKNUM function returns the correct results.
However, the date 15-13-2025 is not a valid date, so the WEEKNUM function returns a #VALUE! error.
For the next input, the formula used is =WEEKNUM(B10, 99). Since 99 is not a valid return_type number, the function is supposed to return a #NUM! error. Otherwise, Excel may ignore the invalid return_type and treat it as 1, and then return a normal result.
A year has about 52 or 53 weeks, depending on leap years. The table below contains the WEEKNUM value for the corresponding week range.
If the year is a leap year, then,
This WEEKNUM function returns different return types for WEEKNUM(), and each one decides which day the week starts. Refer to the table below:
Using the WEEKNUM Function With Other Nested Functions:
Example Explanation:
- In the first example, the WEEKNUM function is used with the date April 10, 2025 (10-Apr-2025) in cell B4. It calculates the week number assuming Sunday as the first day of the week and returns 15.
- In the second example, the formula calculates the week number assuming Monday as the first day of the week. It also returns 15 for the same date.
- In the third example, the formula checks if the week number is even or odd. Since 15 is an odd number, it returns “Odd”.
- In the fourth example, the formula dynamically calculates the current week’s number based on today’s date. The result will automatically update each day.
- In the fifth example, the formula subtracts the current week number from 52 to find out how many weeks are left in the year. For April 10, 2025, it returns 37 weeks remaining.
- In the sixth example, the formula checks whether the week falls in Quarter 2. Since week 15 falls between week 14 and 26, it returns “Q2”.
- In the seventh example, the formula adds about one-fourth of a week’s span to the current date, resulting in May 6, 2025 (06-May-2025).
- In the eighth example, the formula extracts the month number from the date. Since April is the fourth month, it returns 4.
- In the ninth example, the formula classifies the date into either the First Half or the Second Half of the year based on its week number. April 10, 2025, falls in the First Half.
- In the tenth example, the formula combines the year and the week number into a custom format like 2025-W15.
Functions Used in the Examples:
That’s it. This tutorial was originally published on How to Use Excel WEEKNUM Function?