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.
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, optimise 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.
Internally, Excel treats dates as serial numbers – hence the parameter name. The programme 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.
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.
Excel has many more functions that can help you with dates. For example, WORKDAY tells Excel to include only working days.
HiDrive Cloud Storage with IONOS!
Based in Germany, HiDrive secures your data in the cloud so you can easily access it from any device!
Related articles
Rawpixel.comShutterstock How to use the Excel COUNT function
Analyses and calculations in Excel can be highly complex. Excel’s worksheets let you create huge tables, but sometimes you just want to answer a very simple question: How many cells in the table contain a number? In big worksheets, it can be virtually impossible to check the whole thing manually. The COUNT function in Excel was created specifically to solve this problem.
Rawpixel.comShutterstock Excel: The DATEDIF function explained
Microsoft Excel has many functions that help you produce calculations, statistics, and tables. Some of these functions do not even make it into the official list in the software. DATEDIF is one of those hidden functions that can help you calculate periods of time. The result can be shown in days, months or years. We’ll explain how it works.
fizkesShutterstock How to use VLOOKUP in Excel
It can often be incredibly time-consuming to search for an entry in an Excel table manually. This is where VLOOKUP comes into play. This practical function allows you to find the exact value for a specific search criterion. To ensure you can benefit from this practical function, we explain what the function is in simple terms and provide you with VLOOKUP examples.
Ground PictureShutterstock Excel: working with dates
Many countries have their own unique way of displaying dates and times. To be able to switch seamlessly between date and time formats, the information must first be stored in a universal format. You can use the DATE function in Excel for this purpose. In this article, you’ll learn how to work with the Excel DATE formula.
