WEEKNUM in Excel – How to use it

Do you know which calendar week it currently is? Not many people are able to answer this question off the top of their head. But when you are working in Excel, you can use a simple function to find the answer. You can also add the result to other formulas in Excel.

How to use WEEKNUM in Excel quick guide

1. Enter a date in any cell to find the corresponding calendar week.
2. Type =ISOWEEKNUM(B1) in another cell.
3. The calendar week for this particular date will now be displayed in cell B1.

What can Excel’s WEEKNUM function be used for?

People often refer to weeks using their calendar week number, especially in the world of business. This number is determined by sequentially ordering the weeks in a year from 1 to 52. For example, if you are expecting a delivery, you don’t have to define a ‘from-to’ range using days (e.g., between Monday, 21/10/19, and Friday, 25/10/19). Instead you can enter the week number (e.g., in calendar week 43). While it’s not difficult to remember which day it is, very few people are aware of what the current calendar week is.

In Excel, you can use week numbers to sort delivery dates, optimise project planning or simply produce a personal family planner. Luckily, Excel offers a function that assigns a date to the week number: the WEEKNUM function.

What is the syntax for Excel WEEKNUM?

Since Excel derives the week number from a date, you need to enter a date when using the WEEKNUM function. There is also an additional parameter For Excel, the week typically starts on Sunday. However, especially in international contexts, that doesn’t always match the local practices.

=WEEKNUM(serial_number, [return_type])

The first argument contains a date. This refers to another cell that is correctly formatted as a date. Alternatively, you can enter the day directly into the function. To do so, you should ideally use the DATE function.

The second parameter is optional. Using a number, you can tell Excel which day the week should start on. The list below shows which numbers correspond to which weekday. For Sunday and Monday, there are multiple options.

• 1: Sunday
• 2: Monday
• 11: Monday
• 12: Tuesday
• 13: Wednesday
• 14: Thursday
• 15: Friday
• 16: Saturday
• 17: Sunday
• 21: Monday (based on ISO 8601 standard)

Internationally, there is more than one system for counting week numbers. While some businesses count January 1 as part of the first week (no matter which day it falls on), other businesses (especially in Europe) adhere to ISO 8601. This standard states that the week that contains the first Thursday of the year is defined as week 1. So if January 1 is a Friday, the first week starts the following Monday, January 4. With the ISO 8601 standard, the week always starts on Monday.

ISOWEEKNUM lets you specify the week number according to the ISO system without having to use the additional parameter. It works just like the WEEKNUM function, but all you have to do is provide a date.

=ISOWEEKNUM(Date)

WEEKNUM in Excel example

Below, we’ll calculate the current week number for every day of the week in 2019. Here, we’ve used the ISO 8601 system to calculate the week numbers. Here’s the formula:

=WEEKNUM(A1,21)

Now, we’ll apply this Excel formula to every row to determine the week number for every date.

Excel only outputs a number. If you want to add text, you can concatenate elements in your formula.

="W.No. "&WEEKNUM(A1,21)

If you always want the current week number to be displayed in a worksheet, you can do so by combining WEEKNUM with the TODAY function. The latter provides the current date, which you can insert directly in the WEEKNUM function.

=WEEKNUM(TODAY())

The current week number will now always be shown in the worksheet. If you want to modify how weeks are counted, you can also fill in the second parameter of the function.