Updated June 9, 2023
Week Numbers in Excel
It is quite painful to manually find the week number for a specific date. What if I tell you, excel does this job of finding week numbers for you? Yes, you heard it right! Excel can calculate the week number for a specific date or set of dates and give a week number for you with the help of the WEEKNUM function. Yes, only one specific function (less confusion, right?). However, this one function itself could be confusing some of the time. Let’s go through and see how it works and helps us find out the week number in Excel.
The syntax for WEEKNUM Function in;
The syntax for the Excel WEEKNUM function, which outputs the week number from a given date value, is as below:
Where,
- serial_number: is a date value for which you want to calculate the week number. It can be a reference of the cell containing your date value.
- return_type: a number that lets Excel know which day the week has started. This is an optional but very useful argument that can be confusing at the same time.
When the above formula is entered on a date argument, Excel finds the week number for that date and returns a number from 1 to 52 (as there are 52 weeks in a year).
How to use Week Number (WEEKNUM) Function in Excel?
Let’s understand how to use the Week Number Function in Excel by using some examples.
Week Numbers in Excel – Example #1
Simple WEEKNUM Function in Excel (Without [return_type]):
As you might have mentioned above, return_type is an optional argument and can be omitted. If omitted, Excel considers that the first week of the year starts on January 1st, and the last week (i.e., week number 52) will be December 31st.
What if you ignore the return_type? Is it OK? Will Excel still count the week number for a date?
The answer to all these questions is affirmative. Let’s see an example and have a practical understanding of the same.
Suppose we have data, as shown in the image below, with different dates in each cell.
Put the formula =WEEKNUM(A2) in cell B2 and hit Enter.
Excel will return a week value for the date in cell A2. See the screenshot below for a better understanding:
Since we have omitted the return_type, Excel considered that the week starts from January 1st, and it’s a week from Sunday to Sunday (Though 1st January 2017 was a Sunday, It becomes confusing when the 1st day of the year does not fall on Sunday).
Let’s drag the formula for all the cells and see the values for the week number corresponding to every date value.
See the below example for your reference:
This was pretty simple till here, right?
Week Numbers in Excel – Example #2
WEEKNUM Function with return_type:
In the previous section, we ran the function without providing the return_type. This means, by default, the week starts on 1st January and assumes it is Sunday.
But this may not be the case in our interest always. Our week might start on Monday, Tuesday, etc., at the start of the year.
Therefore, the return_type is there.
return_type has the following list of values supported, and you can also see those when you use it under the WEEKNUM formula.
All the above values for argument are used under the WEEKNUM function.
Let’s play with some return_type values and see the magic.
Suppose you wanted your week to be starting from Monday, then give return_type as 2 under the WEEKNUM formula and see if there is any change in week numbers.
See the below screenshot for your reference.
As you can see through highlighted cells, the week number values changed when we used return_type as Monday (i.e., 2). It happened because 1st January 2017 falls on Sunday. So ideally week should have started from the date. But as we have provided the return_type as Monday, Excel considers that the week is about to start from the first Monday of 2017 (2nd January 2017), and hence there is an increase in week value.
Week Numbers in Excel – Example #3
ISOWEEKNUM function:
ISOWEEKNUM function allows you to find out the ISO week number of the week for a particular date. This function is per the ISO standards, which say the week starts on Monday, and the week containing the first Thursday is considered the first week of the year. This function will give you a week value from 1 to 52, specifying on which week the date value falls.
The syntax for ISO week is as above, where the argument named to date is required for the function to calculate the week number as per the ISO standards.
Let’s see some examples of this.
Suppose we have a date of 1st January 2017, which is Sunday. The ISOWEEKNUM function’s value is 52, which denotes the last week of 2016. Because the week starts on Monday under ISOWEEKNUM and the date falls on Sunday.
As 2nd January 2017 falls on Monday, ISOWEEKNUM gives week value as one.
Now suppose the date is 1st January 2015, which is a Thursday. ISOWEEKNUM considers it the first week of the year, and you can see the same under output.
So, these were some of the examples of week numbers. I hope this article helps you get in the concept right. I will suggest having some more practice with different return_type values under the function to get hands-on with it.
Things to Remember
- return_type can be omitted in Excel. However, omitting leads to the start of the week from 1st January of the year, and Excel considers it a Sunday.
- return_type “21” can only be used under the ISOWEEK numbering system.
- ISOWEEKNUM function only requires one argument called date. The cell should contain the actual date for which you wanted to calculate the week number per ISO standards.
- General WEEKNUM functions provide week numbers from 1 to 54. However, the ISOWEEKNUM function provides week numbers only from 1 to 52.
Recommended Articles
This has been a guide to Week Numbers in Excel. Here we discussed How to find week numbers and use the WEEKNUM and ISOWEEKNUM Functions in Excel, along with practical examples and a downloadable Excel template. You can also go through our other suggested articles –
