Home » Excel-Built-In-Functions » Excel-Date-And-Time-Functions » Excel-Weeknum-Function

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
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 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:
  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

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:
  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

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.
Return to the Excel Date and Time Functions Page
Return to the List of All Built-In Excel Functions