How to Use Excel WEEKNUM Function?
The WEEKNUM Function in Excel will return the week number for a given input dates.
TL;DR
The WEEKNUM function returns the week number of a specific date, based on the year. The basic syntax is: =WEEKNUM(serial_number, [return_type]). The serial_number is the date you want to evaluate, and the optional return_type specifies which day the week starts on (1 for Sunday, 2 for Monday). By default, weeks start on Sunday. You can use WEEKNUM Function to convert dates into week numbers for easy reporting. Find more tutorials at Excel24x7.com.
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 function |
|---|---|
| Aim to returns the week number | 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 the Excel will assumes Sunday as value 1.
The following is the difference between the WEEKNUM and ISOWEEKNUM function 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 international ISO 8601 standard consistently |
Basic Example of Using WEEKNUM Function:
The example covers the variable scenarios of using WEEKNUM function in practical days.
| Input Date | Relative Day | Output Result |
|---|---|---|
| 15-Jan-25 | Wednesday | 3 |
| 12-Feb-25 | Wednesday | 7 |
| 15-Mar-25 | Saturday | 11 |
| 10-Apr-25 | Thursday | 15 |
| 25-May-25 | Sunday | 22 |
| 15-13-2025 | 15-13-2025 | #VALUE! |
| 25-Dec-25 | Thursday | 52 |
| 01-Jan-26 | Thursday | 1 |
| Formula Used for | Formula |
|---|---|
| Finding the Relative Day | =TEXT(B4,”dddd”) |
| Finding the WEEK Number | =WEEKNUM(B4) |
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 below table contains the WEEKNUM value to the corresponding week range.
| WEEKNUM Value | Approximate Dates (Range) |
|---|---|
| 1 | Jan 1 – Jan 7 |
| 2 | Jan 8 – Jan 14 |
| 3 | Jan 15 – Jan 21 |
| 4 | Jan 22 – Jan 28 |
| 5 | Jan 29 – Feb 4 |
| 6 | Feb 5 – Feb 11 |
| 7 | Feb 12 – Feb 18 |
| 8 | Feb 19 – Feb 25 |
| 9 | Feb 26 – Mar 3 |
| 10 | Mar 4 – Mar 10 |
| 11 | Mar 11 – Mar 17 |
| 12 | Mar 18 – Mar 24 |
| 13 | Mar 25 – Mar 31 |
| 14 | Apr 1 – Apr 7 |
| 15 | Apr 8 – Apr 14 |
| 16 | Apr 15 – Apr 21 |
| 17 | Apr 22 – Apr 28 |
| 18 | Apr 29 – May 5 |
| 19 | May 6 – May 12 |
| 20 | May 13 – May 19 |
| 21 | May 20 – May 26 |
| 22 | May 27 – Jun 2 |
| 23 | Jun 3 – Jun 9 |
| 24 | Jun 10 – Jun 16 |
| 25 | Jun 17 – Jun 23 |
| 26 | Jun 24 – Jun 30 |
| 27 | Jul 1 – Jul 7 |
| 28 | Jul 8 – Jul 14 |
| 29 | Jul 15 – Jul 21 |
| 30 | Jul 22 – Jul 28 |
| 31 | Jul 29 – Aug 4 |
| 32 | Aug 5 – Aug 11 |
| 33 | Aug 12 – Aug 18 |
| 34 | Aug 19 – Aug 25 |
| 35 | Aug 26 – Sep 1 |
| 36 | Sep 2 – Sep 8 |
| 37 | Sep 9 – Sep 15 |
| 38 | Sep 16 – Sep 22 |
| 39 | Sep 23 – Sep 29 |
| 40 | Sep 30 – Oct 6 |
| 41 | Oct 7 – Oct 13 |
| 42 | Oct 14 – Oct 20 |
| 43 | Oct 21 – Oct 27 |
| 44 | Oct 28 – Nov 3 |
| 45 | Nov 4 – Nov 10 |
| 46 | Nov 11 – Nov 17 |
| 47 | Nov 18 – Nov 24 |
| 48 | Nov 25 – Dec 1 |
| 49 | Dec 2 – Dec 8 |
| 50 | Dec 9 – Dec 15 |
| 51 | Dec 16 – Dec 22 |
| 52 | Dec 23 – Dec 29 |
| 53 (if any) | Dec 30 – Dec 31 (only sometimes) |
If the year is a leap year and then,
| WEEKNUM | Approximate Dates |
|---|---|
| 8 | Feb 18 – Feb 24 |
| 9 | Feb 25 – Mar 2 |
This WEEKNUM function returns different return types for WEEKNUM(), and each one decides which day the week starts. Refer to the table below:
| Return Type | Week Starts On | Comments |
|---|---|---|
| 1 | Sunday | Default system, weeks start on Sunday |
| 2 | Monday | European system, weeks start on Monday |
| 11 | Monday | ISO standard variant, weeks start on Monday |
| 12 | Tuesday | Weeks start on Tuesday |
| 13 | Wednesday | Weeks start on Wednesday |
| 14 | Thursday | Weeks start on Thursday |
| 15 | Friday | Weeks start on Friday |
| 16 | Saturday | Weeks start on Saturday |
| 17 | Sunday | ISO standard where week 1 is the first week with at least 4 days |
Using WEEKNUM Function With Other Nested Functions:
| Input Date: | 10-Apr-25 |
| Scenarios | Formula Used | Output | Comments |
|---|---|---|---|
| Simple WEEKNUM Usage | =WEEKNUM(B4) | 15 | Week number based on default (week starts Sunday) |
| WEEKNUM with Monday Start | =WEEKNUM(B4,2) | 15 | Week number assuming week starts on Monday |
| Check if Week is Even or Odd | =IF(MOD(WEEKNUM(B4),2)=0,”Even”,”Odd”) | Odd | Shows if the week number is even or odd |
| Using WEEKNUM Function with TODAY Function | =WEEKNUM(TODAY()) | 18 | Shows current week’s number based on today’s date |
| Calculate Weeks Left in Year | =52-WEEKNUM(B4) | 37 | How many weeks are left in the year 2025 |
| Highlight if given date lies in Q2 | =IF(AND(WEEKNUM(B4)>=14,WEEKNUM(B4)<=26),”Q2″,”Not Q2″) | Q2 | Checks if date falls into Quarter 2 |
| Add 4 Weeks to Current Date | =B4+WEEKNUM(B4)*7/4 | 06 May 2025 | Adds approximately 1/4th of the week span |
| Find Month from WEEKNUM | =MONTH(B4) | 4 | Displays month number, April is month 4 |
| Custom Text for Early or Late Year | =IF(WEEKNUM(B4)<26,”First Half”,”Second Half”) | First Half | Classify date as First or Second half of the year |
| Combine Week Number and Year | =YEAR(B4)&”-W”&TEXT(WEEKNUM(B4),”00″) | 2025-W15 | Year-Week number format |
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 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:
| Function | Used for |
|---|---|
| WEEKNUM | Returns the week number of a date |
| IF | Performs logical checks to return different values based on a condition |
| MOD | Returns the remainder after a number is divided by a divisor (used to find even/odd) |
| TODAY | Returns the current date without time |
| AND | Checks if multiple conditions are TRUE |
| TEXT | Formats numbers/dates as text in a specified format |
| YEAR | Extracts the year from a date value |
| MONTH | Extracts the month number from a date value |
That’s it.
-Narendhiran Vijayakumar.
Feel free to comment us below, if you have any queries about the above topic and find more interesting excel tutorials on our homepage: Excel24x7.com.
References:
- WEEKNUM function by Microsoft Support, Retrieved on 28/04/2025: Link.
- How To Use Weeknum Function In Excel | How to get the week number in a year based on a date In Excel by The Data Millennials, Retrieved on 28/04/2025: Link.
- WeekNum and ISOWeekNum functions by Learning Microsoft, Retrieved on 28/04/2025: Link.
- How to use WEEKNUM Function in Excel – Complete Guide by ExcelSirJi, Retrieved on 28/04/2025: Link.
- WEEKNUM by Learning Microsoft, Retrieved on 28/04/2025: Link.
- How to find Week Number in Excel‼️Use Amazing Function by Short and Clear Excel, Retrieved on 28/04/2025: Link.
- How to find the week number in current month by Excel Subreddit, Retrieved on 28/04/2025: Link.
- How to find the week number for dates in Excel | use weeknum function by The Excel Guy, Retrieved on 28/04/2025: Link.
Recently Published Tutorials:
- How to Use Excel NETWORKDAYS.INTL Function?
- How to Use Excel NOW Function?
- How to Use Excel NETWORKDAYS Function?
- How to Use Excel MONTH Function?
- How to Use Excel MINUTE Function?
