Excel WEEKDAY and WEEKNUM Functions

last modified October 5, 2026

The WEEKDAY and WEEKNUM functions are essential date functions in Excel. WEEKDAY returns the day of the week for a given date, while WEEKNUM returns the week number of the year. Both take a date as a serial number and an optional return_type that selects a numbering system. This tutorial covers their syntax, every return_type, the ISO 8601 rules, and practical examples.

WEEKDAY Function Basics

WEEKDAY takes a date and returns a whole number that identifies the day of the week. With the default return_type the result runs from 1 (Sunday) through 7 (Saturday), but the optional second argument changes both the starting day and the range.

Component	Description
Function Name	WEEKDAY
Syntax	=WEEKDAY(serial_number, [return_type])
Arguments	serial_number required; return_type optional
Return Value	Whole number from 0 to 7, depending on return_type

This table breaks down the essential components of the WEEKDAY function.

Argument	Meaning
serial_number	The date you want to evaluate. Supply a serial number produced by DATE, a cell reference, or any value Excel can read as a date.
return_type	Optional. A code that selects the numbering system and the day the week begins on. It defaults to 1. An unsupported value returns #NUM!.

WEEKDAY return_type Reference

Each supported return_type maps to a different weekday numbering. The table lists every value accepted by Excel.

return_type	Week starts on	Numbers returned
1 or omitted	Sunday	1 (Sun) through 7 (Sat)
2	Monday	1 (Mon) through 7 (Sun)
3	Monday	0 (Mon) through 6 (Sun)
11	Monday	1 (Mon) through 7 (Sun)
12	Tuesday	1 (Tue) through 7 (Mon)
13	Wednesday	1 (Wed) through 7 (Tue)
14	Thursday	1 (Thu) through 7 (Wed)
15	Friday	1 (Fri) through 7 (Thu)
16	Saturday	1 (Sat) through 7 (Fri)
17	Sunday	1 (Sun) through 7 (Sat)

Types 2 and 11 both start the week on Monday and return the same values; type 11 and the higher codes were added to remove the ambiguity of the older system. Type 3 is the only zero-based option. Any other value, such as 4 or 20, produces a #NUM! error.

Basic WEEKDAY Example

This example demonstrates the simplest use of WEEKDAY to find the day of week.

=WEEKDAY(DATE(2023, 1, 1))

January 1, 2023 was a Sunday, so the formula returns 1 under the default system (1 = Sunday through 7 = Saturday). The date can also be entered as text or as a cell reference, but wrapping it in DATE avoids any text-parsing issues. For example, =WEEKDAY(DATE(2025, 4, 4)) returns 6 because that date was a Friday.

WEEKDAY with Different Return Types

WEEKDAY supports different numbering systems through its optional second argument. This example applies several return types to the same date, April 4, 2025.

Formula	Result	Description
=WEEKDAY(DATE(2025, 4, 4), 1)	6	1 (Sun) through 7 (Sat)
=WEEKDAY(DATE(2025, 4, 4), 2)	5	1 (Mon) through 7 (Sun)
=WEEKDAY(DATE(2025, 4, 4), 3)	4	0 (Mon) through 6 (Sun)
=WEEKDAY(DATE(2025, 4, 4), 11)	5	1 (Mon) through 7 (Sun)
=WEEKDAY(DATE(2025, 4, 4), 17)	6	1 (Sun) through 7 (Sat)

The table shows how return_type changes the same Friday into different numbers. Type 1 is the default and common in the United States, type 2 and type 11 are common in Europe because they start on Monday, and type 3 is zero-based. Types 11 and 17 reproduce the values of types 2 and 1 respectively, so the choice between them is mainly one of clarity.

WEEKNUM Function Basics

WEEKNUM returns the number of the week within the year, from 1 to 53. Unlike WEEKDAY, it answers which week of the year a date falls in rather than which day of the week. The optional second argument selects which day begins a week and which week-numbering system to use.

Component	Description
Function Name	WEEKNUM
Syntax	=WEEKNUM(serial_number, [return_type])
Arguments	serial_number required; return_type optional
Return Value	Week number from 1 to 53

Argument	Meaning
serial_number	The date whose week number you want. Supply a serial number produced by DATE, a cell reference, or a value Excel can read as a date.
return_type	Optional. A code that selects the day a week begins on and the week-numbering system. It defaults to 1. An unsupported value returns #NUM!.

WEEKNUM return_type Reference

Excel supports two week-numbering systems. System 1 numbers the week that contains January 1 as week 1, and the return_type only decides which weekday starts a new week. System 2 follows ISO 8601, where weeks start on Monday and week 1 is the week that contains the first Thursday of the year, which is the same as the week containing January 4.

return_type	Week begins on	System
1 or omitted	Sunday	1
2	Monday	1
11	Monday	1
12	Tuesday	1
13	Wednesday	1
14	Thursday	1
15	Friday	1
16	Saturday	1
17	Sunday	1
21	Monday	2 (ISO 8601)

Basic WEEKNUM Example

This example shows the simplest use of WEEKNUM to find the week number.

=WEEKNUM(DATE(2025, 4, 4))

This formula returns 14, indicating the 14th week of 2025. Under the default system the week containing January 1 is week 1, and every following week starts on Sunday. April 4, 2025 fell in the week that began on Sunday, March 30, which is week 14.

WEEKNUM with Different Return Types

The next table applies three systems to April 4, 2025. In this particular week all three agree, which makes it a useful baseline.

Formula	Result	Description
=WEEKNUM(DATE(2025, 4, 4), 1)	14	Week starts Sunday (default)
=WEEKNUM(DATE(2025, 4, 4), 2)	14	Week starts Monday
=WEEKNUM(DATE(2025, 4, 4), 21)	14	ISO week number

Type 1 is common in the United States, type 2 in Europe, and type 21 follows the ISO 8601 standard with weeks that start on Monday. The three values coincide here only because the date is far from a year boundary. Near January 1 they can differ by one, as the next section shows.

ISO 8601 Week Numbers

For strict ISO 8601 compliance use return_type 21, or the dedicated ISOWEEKNUM function. Under ISO rules a year has 52 or 53 weeks, and the first days of January can belong to the last week of the previous year. The following formulas compare the systems around the start of 2025.

=WEEKNUM(DATE(2025, 1, 5), 1)     ' returns 2, week begins Sunday
=WEEKNUM(DATE(2025, 1, 5), 2)     ' returns 1, week begins Monday
=WEEKNUM(DATE(2025, 1, 5), 21)    ' returns 1, ISO 8601
=WEEKNUM(DATE(2024, 12, 31), 21)  ' returns 1, ISO week 1 of 2025

January 5, 2025 was a Sunday. Because the Sunday-based system starts a new week on that day, it counts week 2, while the Monday-based and ISO systems still count week 1. December 31, 2024 is an even clearer example: ISO 8601 assigns it to week 1 of 2025, but the default system returns 53. Use return_type 21 whenever a date must align with the international standard, and remember that =WEEKNUM(date, 21) and =ISOWEEKNUM(date) are equivalent.

Practical Example: Workday Identification

This example uses WEEKDAY to identify workdays (Monday through Friday) in a date list.

A (Date)	B (Day Type)
2025-04-04	=IF(WEEKDAY(A1,2)<6,"Workday","Weekend")
2025-04-05	=IF(WEEKDAY(A2,2)<6,"Workday","Weekend")

=IF(WEEKDAY(A1,2)<6,"Workday","Weekend")

System 2 numbers Monday as 1 through Sunday as 7, so any value below 6 is a weekday. April 4, 2025 (a Friday) returns "Workday" and April 5, 2025 (a Saturday) returns "Weekend".

Practical Example: Weekday Name Lookup

This example combines WEEKDAY with CHOOSE to display weekday names instead of numbers.

=CHOOSE(WEEKDAY(A1),"Sun","Mon","Tue","Wed","Thu","Fri","Sat")

The formula uses the default numbering, where Sunday is 1, then CHOOSE returns the matching name. If you switch to return_type 2, reorder the list so that the first entry is "Mon", or the names will be shifted by one day.

Practical Example: Fiscal Week Calculation

This example calculates a fiscal week number for a year that starts on April 1, which is useful for financial reporting periods.

=WEEKNUM(A1,2)-WEEKNUM(DATE(YEAR(A1),4,1),2)+1

The formula subtracts the week number of April 1 from the week number of the date and adds 1, so the first week of the fiscal year is numbered 1. It is valid only for dates inside the fiscal year; a date before April 1 produces zero or a negative number, so guard the result with IF if your data can span year ends.

Practical Example: Days Until Next Monday

This example calculates how many days remain until the coming Monday from a given date.

=MOD(8-WEEKDAY(A1,2),7)

System 2 numbers Monday as 1, so the formula measures how far the date is from the next Monday and uses MOD 7 to wrap around. It returns 0 when the date is already a Monday, 1 for Sunday, and so on up to 6 for Tuesday.

WEEKDAY vs WEEKNUM

The two functions answer different questions and are often used together. WEEKDAY locates a date within its week, while WEEKNUM locates it within the year.

Function	Returns	Typical range	Common use
WEEKDAY	Day-of-week index	0 to 7	Weekend tests, schedules, day-name lookup
WEEKNUM	Week number of the year	1 to 53	Reporting periods, ISO weeks, fiscal weeks

Because both share the same serial_number, [return_type] shape, they combine cleanly. A single formula can test whether a date is a workday and also report which week it belongs to, and the same date always agrees across both functions as long as the return_type stays consistent.

WEEKDAY and WEEKNUM Errors and Troubleshooting

Cause	Symptom	Fix
return_type is not a supported code	#NUM! error	Use 1, 2, 3, or 11-17 with WEEKDAY; 1, 2, 11-17, or 21 with WEEKNUM
serial_number resolves outside the supported date range	#NUM! error	Supply a valid date, ideally with DATE
serial_number is text that Excel cannot read as a date	#VALUE! error	Enter a real date or convert the text with DATEVALUE
ISO weeks expected from a default formula	Week number off by one near January 1	Use return_type 21 or ISOWEEKNUM for ISO 8601
Different return_type values used in one report	The same week shows two different numbers	Standardize on one return_type across the workbook
WEEKDAY result used directly as a list index	The wrong weekday name is returned	Match the lookup order to the numbering; type 3 is zero-based
An empty cell is passed as serial_number	0 is treated as a serial number	Validate the cell with ISNUMBER or ISBLANK

The WEEKDAY and WEEKNUM functions are compact tools for working with weekly cycles in Excel. WEEKDAY turns a date into a day-of-week number that drives schedules, weekend tests, and name lookups, while WEEKNUM turns it into a week-of-year number for reporting and planning. The key is to choose the right return_type: type 1 for Sunday-based weeks, types 2 and 11 for Monday-based weeks, type 3 when you need a zero-based index, and type 21 for true ISO 8601 week numbers. Verify the result of each return_type against a known date, keep the choice consistent throughout a workbook, and both functions will make date analysis predictable and reliable.

Author

My name is Jan Bodnar, and I am a passionate programmer with extensive programming experience. I have been writing programming articles since 2007. To date, I have authored over 1,400 articles and 8 e-books. I possess more than ten years of experience in teaching programming.

List all Excel Formulas.