totn Excel Functions

MS Excel: How to use the WEEKNUM Function (WS)

This Excel tutorial explains how to use the Excel WEEKNUM function with syntax and examples.

Description

The Microsoft Excel WEEKNUM function returns the week number from a date value.

The WEEKNUM function is a built-in function in Excel that is categorized as a Date/Time Function. It can be used as a worksheet function (WS) in Excel. As a worksheet function, the WEEKNUM function can be entered as part of a formula in a cell of a worksheet.

Excel WEEKNUM Function

If you want to follow along with this tutorial, download the example spreadsheet.

Download Example

Syntax

The syntax for the WEEKNUM function in Microsoft Excel is:

WEEKNUM( serial_date [, firstdayofweek ] )

Parameters or Arguments

serial_date
The serial date value from which to calculate the week number.
firstdayofweek

Optional. It is a number that specifies the first day of the week for the year. If this parameter is omitted, it defaults to 1. This parameter can be one of the following values:

firstdayofweek Explanation
1 (default) Week starts on Sunday and week containing January 1 is the first week fo the year
2 Week starts on Monday and week containing January 1 is the first week fo the year
11 Week starts on Monday and week containing January 1 is the first week fo the year
12 Week starts on Tuesday and week containing January 1 is the first week fo the year
13 Week starts on Wednesday and week containing January 1 is the first week fo the year
14 Week starts on Thursday and week containing January 1 is the first week fo the year
15 Week starts on Friday and week containing January 1 is the first week fo the year
16 Week starts on Saturday and week containing January 1 is the first week fo the year
17 Week starts on Sunday and week containing January 1 is the first week fo the year
21 Week starts on Monday and the week containing the first Thursday of the year is the first week of the year (commonly known as the European week numbering system)

Returns

The WEEKNUM function returns a numeric value.

Note

  • A serial date is how Excel stores dates internally and it represents the number of days since January 1, 1900.
  • See also the ISOWEEKNUM function if you want to determine the week number using the standard ISO calendar.

Applies To

  • Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007

Type of Function

  • Worksheet function (WS)

Example (as Worksheet Function)

Let's look at some Excel WEEKNUM function examples and explore how to use the WEEKNUM function as a worksheet function in Microsoft Excel:

Excel WEEKNUM Function

Column A displays the serial date parameter for the WEEKNUM function formatted using mmm d, yyyy. Most often you will want a formatted date rather than a serial date.

Column B shows the date from column A as an unformatted serial date.

Based on the Excel spreadsheet above, the following WEEKNUM examples would return:

=WEEKNUM(A2)
Result: 1

=WEEKNUM(A3)
Result: 8

=WEEKNUM(A4)
Result: 14

=WEEKNUM(A5)
Result: 52

=WEEKNUM("12/24/2016")
Result: 52

You can change how the weeks in a year are numbered with the firstdayofweek parameter. If you pass in a parameter of 21 as firstdayofweek, it will renumber the weeks so that the week starts on Monday and the first Thursday of the year is the first week of the year.

For example:

=WEEKNUM("12/24/2016",21)
Result: 51

Now the date "12/24/2016" falls in week 51 instead of week 52, as seen in the previous example. If you want to return the week number for the standard ISO calendar, try using the ISOWEEKNUM function.

Share on: