Excel WEEKNUM Function

This tutorial demonstrates how to use the Excel WEEKNUM Function in Excel to get the week number in a year (1-52).

Week Number Main Function

WEEKNUM Function Overview

The WEEKNUM Function Returns the week number in a year (1-52).

To use the WEEKNUM Excel Worksheet Function, Select cell and Type:

weeknum formula syntax

(Notice how the formula input appear)

WEEKNUM Function syntax and Inputs:

serial_number – The date in Excel serial number format or entered as a date with quotations (“s) surround the date. Example: You can not enter 11/12/2015 directly into the cell. Instead you need to enter “11/12/2015” or you would need to use the corresponding serial number: 42320. Alternatively, you can reference a cell with the date 11/12/2015 entered. Excel automatically converts dates stored in cells into serial format (unless the date is entered as text).

return_type – OPTIONAL. A number indicating which day is the start of the week. 1 (Default) for Sunday, 2 for Monday.

Calculate Week Number of a Date

The WEEKNUM Function calculates the week number of a date:

Week NumberThe WEEKNUM function will return a value between 1 and 54. Why 54?  The function starts counting weeks on the Sunday (by default) of the week containing January 1st. This can result in 54 unique weeks in the year.

To change the weekday start, use this chart:

Return type Table

This example will start the WEEKNUM function on a Monday instead:

Week Number MONDAYWEEKNUM in Google Sheets

The WEEKNUM Function works exactly the same in Google Sheets as in Excel:

Week Number Google sheet

 

WEEKNUM Examples in VBA

vba code generator
You can also use the WEEKNUM function in VBA. Type:
Application.Worksheetfunction.Weeknum(serial_number,return_type)

 

We can use the WEEKNUM function as follows:

 

The result we will get is:

Vba WEEKNUM function

For the function arguments (serial_number, etc.), you can either enter them directly into the function, or define variables to use instead.

Return to the List of all Functions in Excel