Excel WEEKNUM Function

Related Function:

WEEKDAY

Function Description

For a given date, the Excel Weeknum function returns an integer representing the week number (from 1 to 53) of the year.

The syntax of the function is:

WEEKNUM( serial_number, [return_type] )

Where the arguments are as follows:

serial_number-The Excel date that you want to return the week number for.
[return_type]-

An optional argument, that specifies which numbering system to be used and which weekday should be treated as the start of the week.

The two different numbering systems are:
system 1     -The week containing January 1st is numbered week 1;
system 2     -The week containing the first Thursday of the year is numbered week 1.
The possible values of [return_type] are:

[return_type]Meaning
1Week runs from Sunday to Saturday; Uses numbering system 1
2Week runs from Monday to Sunday; Uses numbering system 1
11Week runs from Monday to Sunday; Uses numbering system 1
12Week runs from Tuesday to Monday; Uses numbering system 1
13Week runs from Wednesday to Tuesday; Uses numbering system 1
14Week runs from Thursday to Wednesday; Uses numbering system 1
15Week runs from Friday to Thursday; Uses numbering system 1
16Week runs from Saturday to Friday; Uses numbering system 1
17Week runs from Sunday to Monday; Uses numbering system 1
21Week runs from Monday to Sunday; Uses numbering system 2

If omitted, the [return_type] argument is set to the default value 1 (i.e. the function assumes that a week starts on Sunday and numbering system 1 is used).

Note that only options 1 and 2 above are available in Excel 2007 and earlier.



Note that it is advised that you do not type dates directly into functions, as Excel may interpret text representations of dates differently, depending on the date interpretation settings on your computer. Therefore the serial_number argument for the Weeknum function should be input as either:

    or

Weeknum Function Examples

Column B of the spreadsheet below contains 5 examples of the Excel Weeknum Function.

 Formulas:
 AB
1Sat 01-Jan-2011=WEEKNUM( A1 )
2Sun 02-Jan-2011=WEEKNUM( A2 )
3Sun 02-Jan-2011=WEEKNUM( A3, 2 )
4Mon 03-Jan-2011=WEEKNUM( A4 )
5Mon 03-Jan-2011=WEEKNUM( A5, 2 )
 Results:
 AB
1Sat 01-Jan-20111
2Sun 02-Jan-20112
3Sun 02-Jan-20111
4Mon 03-Jan-20112
5Mon 03-Jan-20112

Weeknum Function Example 2

The Excel Weeknum function may be useful if you want to analyse the peak weeks throughout the year for a set of events.

Column A of the spreadsheet below records the dates of a set of events, and the Weeknum function is used in column B of the spreadsheet, to show the week number for each event.

 Formulas:
 AB
1DateWeek No.
2Tue 01-Jan-2008=WEEKNUM( A2 )
3Wed 02-Jan-2008=WEEKNUM( A3 )
4Wed 02-Jan-2008=WEEKNUM( A4 )
5Fri 04-Jan-2008=WEEKNUM( A5 )
6Mon 07-Jan-2008=WEEKNUM( A6 )
7Mon 07-Jan-2008=WEEKNUM( A7 )
8Wed 09-Jan-2008=WEEKNUM( A8 )
9Sat 12-Jan-2008=WEEKNUM( A9 )
10Sun 13-Jan-2008=WEEKNUM( A10 )
11Mon 14-Jan-2008=WEEKNUM( A11 )
12Mon 14-Jan-2008=WEEKNUM( A12 )
13Tue 15-Jan-2008=WEEKNUM( A13 )
14Wed 16-Jan-2008=WEEKNUM( A14 )
15Wed 16-Jan-2008=WEEKNUM( A15 )
16Wed 16-Jan-2008=WEEKNUM( A16 )
17Mon 21-Jan-2008=WEEKNUM( A17 )
18Mon 21-Jan-2008=WEEKNUM( A18 )
19Fri 25-Jan-2008=WEEKNUM( A19 )
20Wed 30-Jan-2008=WEEKNUM( A20 )
21Sat 02-Feb-2008=WEEKNUM( A21 )
22Sun 03-Feb-2008=WEEKNUM( A22 )
23Sun 03-Feb-2008=WEEKNUM( A23 )
24Mon 04-Feb-2008=WEEKNUM( A24 )
 Results:
 AB
1DateWeek No.
2Tue 01-Jan-20081
3Wed 02-Jan-20081
4Wed 02-Jan-20081
5Fri 04-Jan-20081
6Mon 07-Jan-20082
7Mon 07-Jan-20082
8Wed 09-Jan-20082
9Sat 12-Jan-20082
10Sun 13-Jan-20083
11Mon 14-Jan-20083
12Mon 14-Jan-20083
13Tue 15-Jan-20083
14Wed 16-Jan-20083
15Wed 16-Jan-20083
16Wed 16-Jan-20083
17Mon 21-Jan-20084
18Mon 21-Jan-20084
19Fri 25-Jan-20084
20Wed 30-Jan-20085
21Sat 02-Feb-20085
22Sun 03-Feb-20086
23Sun 03-Feb-20086
24Mon 04-Feb-20086

Excel Weeknum Pivot Table

The WEEKNUM data can now be used in an Excel Pivot Table, to show the number of events occurring during each week. A pivot table made up of the above data is shown aboveon the right.

The pivot table enables you to clearly see that the peak week for the events is week number 3 (13th-19th January), which accounts for a total of 7 events.


For further information and examples of the Excel Weeknum function, see the Microsoft Office website.


Weeknum Function Errors

If you get an error from the Excel Weeknum function, this is likely to be one of the following:

Common Errors
#NUM!-

Occurs if either:

  • The supplied [return_type] argument is not one of the permitted values (see above table).
or
  • The supplied serial_number argument is numeric but is out of range for the current date base.
#VALUE!-Occurs if the supplied serial_number cannot be recognised as a numeric value or a date.