Excel Week Numbers (Table of Contents)
Week Numbers in Excel
It is quite a painful job to find out week number for a specific date manually. What if I tell you, excel does this job of finding week number for you? Yes, you heard it right! Excel can calculate week number for a specific date or set of dates and give a week number for you with the help of WEEKNUM function. Yes, only one specific function (less confusion, right?). However, this one function itself could be confusing some of the times. Let’s go through and see how it works and helps us find out week number in excel.
Syntax for WEEKNUM Function in;
Syntax for excel WEEKNUM function which outputs week number from a given date value is as below:
- serial_number: is a date value for which you want to calculate the week number. It can be a reference of the cell which is containing your date value.
- return_type: is a number which can let excel know on which day the week has started. This is an optional but really very useful argument which can be confusing at the same time.
When the above formula is entered on a date argument, Excel finds out 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 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 paid attention above, return_type is an optional argument and can be omitted. If omitted, the excel considers that the first week of the year starts on January 1st and 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, which has 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 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 actually 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 week number corresponding to every date value.
See 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. Which means by default the week starts on 1st January and assumes that it is Sunday.
But which may not be the case of 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 WEEKNUM function.
Let’s play around with some of the return_type values and see the magic.
Suppose you wanted your week to be starting from Monday, then give return_type as 2 under WEEKNUM formula and see if any change in week numbers.
See below screenshot for your reference.
As you can see through highlighted cells, the week number values have changed when we have used return_type as Monday (i.e. 2). It happened because 1st January 2017 is falling 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 first Monday of 2017 (which is 2nd January 2017) and hence there is an increase in week value.
Week Numbers in Excel – Example #3
ISOWEEKNUM function allows you to find out the ISO week number of the week for a particular date. This function is as per the ISO standards which says, the week starts on Monday and week containing the first Thursday is considered as the first week of the year. This function will give you 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 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 1st January 2017 which is Sunday. The value ISOWEEKNUM function gives is 52 which denotes last week of 2016. Because the week starts on Monday under ISOWEEKNUM and the date is falling on Sunday.
As 2nd January 2017 falls on Monday, ISOWEEKNUM gives week value as one.
Now suppose a date 1st January 2015 which is a Thursday. ISOWEEKNUM considering it as the first week of the year and same you can see under output.
So, these were some of the examples on week numbers. 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 gets 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 that it’s a Sunday.
- return_type “21” can only be used under ISOWEEK numbering system.
- ISOWEEKNUM function only requires one argument called date. It should be the cell which contains the actual date for which you wanted to calculate the week number as per ISO standards.
- General WEEKNUM functions provide week numbers from 1 to 54. However, ISOWEEKNUM function provides week numbers only from 1 to 52.
This has been a guide to Week Numbers in Excel. Here we discussed How to find week numbers and How to use WEEKNUM and ISOWEEKNUM Function in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –