Summary

The Excel WEEKNUM function takes a date and returns a week number (1-54) that corresponds to the week of year. The WEEKNUM function starts counting on the week that contains January 1. By default, weeks begin on Sunday, but this can be changed.

Purpose 

Get the week number for a given date

Return value 

A number between 1 and 54.

Syntax

=WEEKNUM(serial_num,[return_type])
  • serial_num - A valid Excel date in serial number format.
  • return_type - [optional] The day the week begins. Default is 1.

How to use 

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. Typically the last week number in a year is 53. However, WEEKNUM will return 54 at the end of some years, like 2000 and 2028.

The WEEKNUM function accepts two argumentsserial_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.

Return_type Week begins
1 (default) Sunday
2 Monday
11 Monday
12 Tuesday
13 Wednesday
14 Thursday
15 Friday
16 Saturday
17 Sunday
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 the week a new week number should begin. 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 with native Excel dates is more reliable. To create a date from scratch in a formula, you can use the DATE function.

Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.