Excel WEEKNUM function

What number is this calendar week? Many people rarely know the right answer to this question. But when you are working in Excel, you can use a very simple function to find the answer. If necessary, you can add the result to other formulas.

Excel with Microsoft 365 and IONOS!

Use Excel to create spreadsheets and organize your data - included in all Microsoft 365 package!

Office Online
OneDrive with 1TB
24/7 support

What do you need Excel’s WEEKNUM function for?

Especially in business, people often refer to weeks by the calendar week number. The numbers refer to consecutively numbered weeks of a year. For example, if you are expecting a delivery in a specific week, you don’t have to define a “from-to” range using days (“between Monday, 10/21/19, and Friday, 10/25/19”). All you have to enter is the week number (“in calendar week number 43”). While you generally know the days, very few people know the current week number.

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

Syntax for Excel week number

Excel derives the week number from a specific date. Accordingly, the date must naturally be part of the WEEKNUM function. You can also select another optional setting. For Excel, the week typically starts on Sunday. However, especially in international contexts, that doesn’t always match the local practices. The system used is very important for the counting method, as you can easily be off by a week otherwise.

=WEEKNUM(Serial_num,[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. That way, you can ensure that Excel processes the value correctly.

Fact

Internally, Excel treats dates as serial numbers – hence the parameter name. The program counts consecutively upwards, starting from January 1, 1900. Theoretically, you can also enter this number directly, but the DATE function can perform this step for you.

The optional second parameter is defined by a number. The list below shows the numbers for each weekday.

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

There is more than one system internationally for counting week numbers. While some people count January 1 as part of the first week (no matter which day it is), others (especially in Europe) adhere to standard ISO 8601. This standard defines that the first week of the year with a Thursday is week 1. So if January 1 is a Friday, the first week will not start until the following Monday. You can use the optional parameters to adapt the system to the two different approaches. Value 21 is the standard in which the week always starts with a Monday.

Tip

If you do not assign the second parameter, Excel will always automatically assume value 1, i.e. the week starts with Sunday.

Since Excel 2013, there has also been a function that does not require additional parameters to specify the week number according to the ISO system. ISOWEEKNUM works just like WEEKNUM otherwise. All you have to do is provide the function with a date.

=ISOWEEKNUM(Date)

Excel: WEEKNUM explained using an example

Let’s take a simple calendar as an example. We want to know the current week number for every day of the week. Here, we will use the ISO 8601 system for counting week numbers.

=WEEKNUM(A1,21)

Now you can apply this formula to every row, giving you the correct week for every date.

Excel only outputs a simple number. If you want to link the result with another text, you can concatenate multiple elements in the formula.

="week no. "&WEEKNUM(A1,21)

If you always want to show the current week number in a worksheet, you can do so by combining WEEKNUM with the practical TODAY function. The latter gives the current date in the right format. As a result, you can insert the function directly in WEEKNUM.

=WEEKNUM(TODAY())

The current week number is now always shown in the worksheet. You can also fill the second parameter in this context to adapt the counting method.

Tip

Excel has many more functions that can help you with dates. For example, WORKDAY tells Excel to include only working days.

Excel with Microsoft 365 and IONOS!

Use Excel to create spreadsheets and organize your data - included in all Microsoft 365 package!

Office Online
OneDrive with 1TB
24/7 support

We’re all in this together. IONOS is
#HereForYou
For your business. When times are tough. When it matters most.
Keep your business going or start one with your own online store.
3 months free
Online Store
Get started in the world of eCommerce free for 3 months.