Excel WEEKNUM function

If you want to follow along with this tutorial, please download the example spreadsheet.

Description

The WEEKNUM in Excel returns the week number of the given date in a year, which starts counting weeks from January 1. By default, the WEEKNUM function count week begins on Sunday.

Syntax and arguments

Formula syntax

Arguments

System

The WEEKNUM function can be used in two systems.

System 1 The function counts week start on January 1, which means January 1 in every year is the first week.

System 2 The function counts week starts on the first Thursday of a year, take an instance, 1/1/2020 is not the first week, as it is Wednesday. However, 1/2/2020 is the first week, as it is the first Thursday in year 2020.

Return Value

The WEEKNUM function returns the week number (1-54).

Remarks

A serial date is how Excel stores dates internally and it represents the number of days since January 1, 1900. For example, January 1, 1900 is serial number 1, January 1, 2020 is serial number 43831 because it has been 43831 days after January 1, 1900.

Error

If the argument serial_num is out of range for the current date base value, it returns an error value #NUM!. For example, if the serial_num (-3) is negative value or a huge number (100000000), it returns error value.

If the return_type is out of range specified in table above, it returns an error value #NUM!.

Usage and Examples

Here is a list of dates in range B3:B6, to get their week number and the week begins with Sunday please use below formula:

=WEEKNUM(B3)
Or
=WEEKNUM(B3,1)
Or
=WEEKNUM(B3,17)

Press Enter key and drag fill handle down to cell B6.

Relative Functions:

• Excel Date Function
  Combine year, month and day numbers from separate cells to a valid date.

• Excel DATEDIF Function
  If you want to calculate the number of years, months, days or weeks between two given dates, the DATEDIF function in Excel may help you.

• Excel DATEVALUE Function
  DATEVALUE function can help you to convert date that stored as text format to a serial number which Excel can recognize as a date, and then format the number to proper date format.

• Excel DAY Function
  With the DAY function, you can quickly get the day as a number from 1 to 31 based on the given dates.