How to Use Excel WEEKNUM Function?

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: 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: 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

May 10, 2025 - 17:16

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:

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:

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.