The WEEKNUM is a Date & Time function that finds the week number of a given date. WEEKNUM function returns an integer indicating the week index according to the specified return type. In this guide, we’re going to show you how to use the WEEKNUM function and also go over some tips and error handling methods.


Supported versions

  • All Excel versions

Excel WEEKNUM Function Syntax

WEEKNUM(serial_number, [return_type])


Arguments

serial_number The date of the week number you want. Microsoft recommends using the DATE or other similar functions that return a date serial number.
[return_type] Optional. A number determines day of week mapping scheme. The default value is 1. See the table below for valid numbers.

Week Number Return Types

Return_type Week begins on Week Number Assumption
1 or omitted Sunday The week containing January 1 is the first week of the year.
2 Monday The week containing January 1 is the first week of the year.
11 Monday The week containing January 1 is the first week of the year.
12 Tuesday The week containing January 1 is the first week of the year.
13 Wednesday The week containing January 1 is the first week of the year.
14 Thursday The week containing January 1 is the first week of the year.
15 Friday The week containing January 1 is the first week of the year.
16 Saturday The week containing January 1 is the first week of the year.
17 Sunday The week containing January 1 is the first week of the year.
21 Monday The week containing the first Thursday of the year is the first week of the year. European week numbering system based on ISO 8601.

Example

To find the week number of a date, provide a date for serial_number argument. Microsoft recommends using serial numbers, instead of date strings like “2/5/2019”. A serial number date can be generated via functions like DATE.

You can also set a number for the return_type argument. The number specifies the week numbering scheme. You can omit the argument to return a number based on a week that starts on a Sunday and ends on Saturday.

=WEEKNUM(DATE(2018,5,13),11) returns 19. The WEEKNUM function assumes a week starts on Monday and January 1 is the first week of the year.

week number

Download Workbook


Tips

  • Use the DATE function to generate a date serial number.
  • [return_type] 1-17 assumes the week number 1 in a given year is assigned to the week that contains January 1.
  • [return_type] 21 assumes the week containing the first Thursday of the year, following ISO 8601.
  • Excel keeps date and time values as numbers. Excel assumes that Jan 1st, 1900 is 1, and every subsequent date value is based on this. While whole numbers represent days, decimals represent time values. For example; 1/1/2018 is equal to 43101, and 12:00 is equal to 5.

Issues

  • If the start_date is not a valid date, the WEEKNUM function returns the #VALUE! error. Microsoft recommends using a formula to generate a valid date serial value to be used in finding the week number.