Summary
The Excel WEEKNUM function calculates the week number of the year for a given date. By default, the function starts counting weeks from the first Sunday of the year, but this start day can be modified.
Syntax
=WEEKNUM(serial_num, [return_type])
- serial_num: The date for which the week number is required.
- return_type: [Optional] A number specifying the first day of the week. Default is 1 (Sunday).
Return value
A number between 1 and 54, representing the week of the year.
How to use
The WEEKNUM function is used to determine the week number of a given date within a year. It supports different return types to specify which day the week starts on:
1 (Default): Week starts on Sunday.2: Week starts on Monday.11–17: Custom configurations for different starting days (Monday to Sunday)21: Follows ISO 8601, with week 1 as the week containing the first Thursday of the year.
Examples
Simple WEEKNUM
Finding the Week Number of a Specific Date:
=WEEKNUM("2021-04-07")
Using return type 2, this formula calculates the week number with Monday as the first day of the week.
WEEKNUM with Return Type
Customizing Start Day of the Week:
=WEEKNUM("2021-04-07", 2)
Using return type 2, this formula calculates the week number with Monday as the first day of the week.
WEEKNUM for Year Transition
Week Number Across Year End:
=WEEKNUM("2020-12-31")
Calculates the week number for December 31, 2020.
=WEEKNUM("2021-01-01")
Calculates the week number for January 1, 2021, showing how the week number resets with the new year.
Additional Notes
- The WEEKNUM function will return a value even for empty dates, so consider handling this if blank dates are possible.
- Useful in scenarios that require tracking or scheduling on a weekly basis.
- The choice of return_type allows for flexibility in defining the start of the week to align with organizational or regional standards.
