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:

Parameters

1. serial_number: This is a date value for which we all want to know or calculate the week number. And it could be a reference of the particular cell containing our date value respectively.
2. return_type: This is a number that can let Microsoft excel to know which day the week has been started. And it is optional but can be considered as the valuable argument of the "WEEKNUM" function which might be confusing sometimes.

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:

1. System 1: The weeks containing 1st January are primarily considered as the 1^st week of the year and numbered as week 1. And in this type of week numbering system, the week eventually starts on Sunday.
2. System 2. System 2 (week numbering system) is the ISO week date system which is the practical part of the ISO 8601 date as well as the time standard. And in this type of system, the week will start from Monday and the week which are containing the first Thursday of the year is considered as week 1. This system is known as the 'European week numbering system', used in the government and business sector for timekeeping and fiscal years.

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 1^st January 2017 was a Sunday, so it becomes a little bit confusing when the 1^st 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 1^st 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 1^st 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 1^st 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 2^nd January 2017 falls on Monday, ISOWEEKNUM gives week value as one.

Now we will be supposing a date as 1^st 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:

1. The return_type can be easily omitted in Microsoft Excel, and this omission will lead to the starting of the week from 1st January (of that current year), and the excel would consider it Sunday.
2. The return_type "21" in Microsoft Excel should be used only under the ISOWEEK numbering system respectively.
3. The "ISOWEEKNUM" function in Excel would require only a single argument called 'date', and it should be the only cell responsible for containing the actual date for which an individual needs to calculate the week number in accordance with the ISO standards efficiently.
4. And the General WEEKNUM function provides the week numbers ranging from 1 to 54 to each working on the excel sheet, while the ISOWEEKNUM FUNCTION provides the week numbers going from 1 to 52.