- serial_num - A valid Excel date in serial number format.
- return_type - [optional] The day the week begins. Default is 1.
The WEEKNUM function takes a date and returns a number between 1 and 54 that corresponds to the week of the year. By default, the WEEKNUM function starts counting on the week that contains January 1 and increments week numbers on Sunday.
The WEEKNUM function accepts two arguments, serial_num and return_type. The serial_num argument must have a valid Excel date. The return_type argument controls what day of the week begins a new week number. Return_type is optional and defaults to 1, which sets new week numbers to start on Sunday. When return_type is set to 2, week numbers begin on Monday.
With a return_type of 1-17, week number 1 in a given year is assigned to the week that contains January 1. With return_type 21, week 1 is the week containing the first Thursday of the year, following ISO 8601. The table below summarizes return_type options.
|21||Monday (see note above)|
Example #1 - basic usage
The formulas below return the week number for the last day of 2020 and the first day of 2021:
=WEEKNUM("31-Dec-2020") // returns 53 =WEEKNUM("1-Jan-2021") // returns 1
Example #2 - return type
The return_type argument controls what day of week begins a new week number. By default, return_type is 1, and numbers increment on Sunday. When return_type is provided as 2, week numbers begin on Monday. For example, January 3, 2021 is a Sunday and, WEEKNUM will return 2, since new numbers start on Sundays:
=WEEKNUM("3-Jan-2021") // returns 2
However, when return_type is set to 2, WEEKNUM will return 1 and start week 2 on Monday:
=WEEKNUM("3-Jan-2021",2) // returns 1 =WEEKNUM("4-Jan-2021",2) // returns 2
Note: the examples above show dates as text values for readability, but working native Excel dates is more reliable. To create a date from scratch in a formula, use the DATE function.