WEEKNUM Function

Basic Description

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

Syntax: 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
1 Week runs from Sunday to Saturday; Uses numbering system 1
2 Week runs from Monday to Sunday; Uses numbering system 1
11 Week runs from Monday to Sunday; Uses numbering system 1
12 Week runs from Tuesday to Monday; Uses numbering system 1
13 Week runs from Wednesday to Tuesday; Uses numbering system 1
14 Week runs from Thursday to Wednesday; Uses numbering system 1
15 Week runs from Friday to Thursday; Uses numbering system 1
16 Week runs from Saturday to Friday; Uses numbering system 1
17 Week runs from Sunday to Monday; Uses numbering system 1
21 Week 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 Microsoft advises 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_num argument for the Weeknum function should be input as either a reference to a cell containing a date or a date returned from another function or formula

 

Weeknum Function Examples

The spreadsheet below shows 5 examples of the Excel Weeknum Function. The formulas are shown in the spreadsheet on the left and the results are shown in the spreadsheet on the right.

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

 

Weeknum Function Example 2

One use for the Excel Weeknum function is if you have a large number of events and you want to analyse the peak weeks throughout the year. The spreadsheet below shows part of a spreadsheet in which the days of specific events are stored in column A, and the Weeknum function is used in column B, to show the week number of each event.

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

 

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 from the small sample of data above is shown on 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.

Excel Weeknum Pivot Table

 

 

Weeknum Function Errors

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

#VALUE! – Occurs if the supplied serial_num cannot be recognised as a numeric value or a date.
#NUM! – Occurs if either the supplied [return_type] argument is not one of the above listed permitted values or the supplied serial_num argument is numeric but is out of range for the current date base.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

code

clearPost Comment