Microsoft Excel’s WEEKNUM Function returns a number that indicates where that particular week falls numerically within a year.
The format for this function is
So, let’s use this function to find out what week number 12 December 2010 falls into. In my example cell A2 contains the date 12/12/2010. So click in cell B2 and type the following:
You will then get an answer of 51. Here it is important to learn about the return type value. You will see I did not specify it on the function above. The return value has the option of either 1 or 2. They each specify a different start day for a week.
1 = week starts on a Sunday
2 = week starts on a Monday
Let’s change the function slightly and see the result if we were to add either 1 or 2 as the return type. In my example I have added the same date to cell A3 for comparison.
You will note that because 12 December 2010 is a Sunday it falls in week 51 for a return value of 1. But it falls within week 50 if you specify a return value of 2.
The WEEKNUM function considers the week containing January 1 to be the first week of the year. However, the European standard defines the first week as the one with four or more days falling in the new year. This means that for years in which there are three days or less in the first week of January, the WEEKNUM function returns week numbers that are incorrect according to the European standard.
If you find that you get a #NAME? error when using this function then you need to install the Analysis ToolPak add in.
If this function is not available, and returns the #NAME? error, install and load the Analysis ToolPak add-in.
I hope you found this post useful?