Excel WEEKNUM function

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.
  • 1117: 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.

Related Functions

Excel ISOWEEKNUM function

The Excel ISOWEEKNUM function calculates the ISO week number for a date, aiding in standardized planning and scheduling tasks.

Content Navigation