how to use excel weeknum function

Get week number of a date.

WEEKNUM Function can help you to get the week number of a date. In simple words, it will return week number from the total 52 weeks in a year for the date you specify.

Let’s say, if you specify 31-Dec-2016, it will return 52 in the result. The reason is that 31-Dec-2016 is in the week 52.

Syntax

WEEKNUM(serial_number,return_type)

serial_number  A serial number which represents the date for which you want to get the week number.

return_type  A number to specify the starting day of the first week of the year. You have two systems to specify starting date of the week.

  • System 1:    In this system, the week which has January 1 is the first week of the year.
  • System 2:   In this system, the week containing the first Thursday of the year is the first week of the year. This system is based on the European week numbering system.

You can use following numbers for the return type.

NumberDescription
1 or omittedSystem 1: Starting From Sunday
2System 1: Starting From Monday
11System 2: Starting From Monday
12System 2: Starting From Tuesday
13System 2: Starting From Wednesday
14System 2: Starting From Thursday
15System 2: Starting From Friday
16System 2: Starting From Saturday
17System 2: Starting From Sunday
21System 2: Starting From Monday

More Information on Excel WEEKNUM Function

  • Excel stores the dates in serial numbers so that you can only use date which has the valid serial numbers.
  • If the serial number of date is out of range for the current date base value, a #NUM! is returned.
  • If the return type is out of the range specified in the table above, a #NUM! is returned.

Examples

In below example, I have used Today Function with weeknum to get the week number of the current date. It will update the week number automatically every time when the date changes.

Examples To Understand Excel Weeknum Function With Today Function

In below example, I have added text “Week-” with the week number for a meaningful result. This can help me to categorize date on week basis.

Examples To Understand Excel Weeknum Function

 

Sample File

download sample file to learn about excel weeknum function

What’s Next?

To learn more about Excel WEEKNUM Function you can check Microsoft’s Help Section. And, if you have a unique idea to use the weeknum function, I would love to hear from you.

There is also you have WEEKDAY  function which is highly useful like weeknum function. And apart from this, I have a list of excel functions and some real life formulas examples.