WEEKNUM Fx – Week Number of Date – Excel, VBA, & G Sheets

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

Calculate Week Number of a Date

The WEEKNUM Function calculates the week number of a date:

=WEEKNUM(B4)

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

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

=WEEKNUM(B4,2)

WEEKNUM in Google Sheets

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

WEEKNUM Examples in VBA

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:

Range("B2") = Application.WeekNum(Range("A2"))
Range("B3") = Application.WeekNum(Range("A3"))
Range("B4") = Application.WeekNum(Range("A4"))

The result we will get is:

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