Calculating week number in Microsoft Excel with the help of the WEEKNUM function
We all know that manually finding of the week numbers for a specific given date is quite a painful job, but what if I tell you all that Microsoft excel does this job of finding the week numbers for you very easily? Yes, you all heard it right! Microsoft Excel can calculate the week numbers for the given specific date or for the different sets of dates and will give out the week numbers as an output for us with the help of the "WEEKNUM" function.
The syntax for WEEKNUM Function in Microsoft Excel
The "WEEKNUM" function is extensively used in Microsoft Excel for the purpose of returning the week number of a given specific date in the year (a number lies between 1 and 54). The "WEEKNUM" function is incorporated with two arguments in which the first one is required while the second one is optional:
Here is the complete list of the return_type values which are supported in "WEEKNUM" formulas in Microsoft Excel:
Moreover, in the "WEEKNUM" function, there are two different types of week numbering systems used, which are as follows:
All of the returns types we have listed above will apply to System 1, leaving aside the return type 21 as it was used in System 2.
Note: In Microsoft Excel 2007 and in the earlier versions, only options 1 and 2 are available while return types from 11 to 21 are supported in Excel 2010 and Excel 2013 only.
And when we enter the above formula on a given date argument, Microsoft Excel will find out the week numbers for that respective date argument and return a number which ranges from 1 to 52, this is because there are 52 weeks in a year respectively.
How to make use of the Week Number (WEEKNUM) Function in Microsoft Excel?
Let us now understand how to make use of the Week Number Function in Microsoft Excel with the help of the various examples:
# Example 1: Simple WEEKNUM Function in Microsoft Excel (Without [return_type])
And if we might have paid attention to the above return_type, which is an optional argument and can be omitted, if we are omitting it, the Microsoft Excel would consider 1st January as the first week of the year and 31st December as the last week of the year.
What if we all ignore the return_type? Is it OK? Will Microsoft Excel still count the week number for a date?
So the answer to all these questions is affirmative, and we will see an example to have a clear and practical understanding of the same.
Suppose that we have a dataset that has different dates in each cell:
After that, we will put the formula =WEEKNUM (A2) in the B2 cell and press the "Enter" button from the keyboard.
And Microsoft Excel will return a week value for the given date in cell A2, respectively.
As we have omitted the return_type, Microsoft Excel would be considering that the week must start from 1st January, and it is a week from Sunday to Sunday (Though 1st January 2017 was a Sunday, so it becomes a little bit confusing when the 1st day of the year would not fall on Sunday).
So now let us drag out the particular formula to all the cells and get encountered with the values for the week number corresponding to every date value.
# Example 2: WEEKNUM Function with return_type
In the previous example, we have discussed that we ran the function without providing the return_type, which means by default, the week needs to start from 1st January, assuming it would be Sunday.
But this is not the case in our interest; our week could start from Monday, Tuesday etc., at the start of the year respectively. And that is why we should always make use of the return_type.
The return_type has lots of values supported in Microsoft Excel, and we can also see those when we make use of it under the WEEKNUM formula effectively:
All the above values for the given argument are primarily used under the WEEKNUM function.
Now let us play with some of the return_type values and see the magic. Suppose that we want our week to be got started from Monday itself then, in that case, we will be providing the return_type as 2 under the "WEEKNUM" formula and then will see the output:
As per the highlighted cells, the particular week number values might have changed when we used the return_type as Monday (i.e. 2). It happened because 1st January 2017 falls on Sunday, and the week should have started from the date.
# Example 3: ISOWEEKNUM function in Excel
ISOWEEKNUM function in Microsoft Excel would allow us to find out the ISO week number of the week for the given particular date, as per the ISO standards, which say "the week must start on Monday. The week containing the first Thursday is considered as the first week of the year".
And this function will give us a week value ranging from 1 to 52, specifying on which week the date value falls respectively:
The syntax for ISO week is mentioned above, in which the argument is named as a date primarily required for the function to calculate the week number in accordance with the ISO standards.
Let us see some of the other examples for a better understanding:
In this example, we will assume that we have a date as 1st January 2017, which is Sunday, and the value the ISOWEEKNUM function gives is 52, which denotes the last week of 2016. Because the week starts on Monday under ISOWEEKNUM and the date falls on Sunday effectively:
As 2nd January 2017 falls on Monday, ISOWEEKNUM gives week value as one.
Now we will be supposing a date as 1st January 2015, which is a Thursday. ISOWEEKNUM considers it the first week of the year, and we can see the same under the output.
What is the important point which needs to be remembered in Microsoft Excel?
The various essential points, as well as the things that need to be remembered by every individual while calculating the weekly number with the help of the "WEEKNUM" function, are as follows: