Its only major drawback is that it will give you the week’s date range but not the week number. The advantage of this approach is that CHOOSE allows arbitrary values for each day of the week you can customize as you like. ►WEEKNUM(Table13+0) returns an array of week numbers for the given date range )=87.314 SUM BY WEEK USING PIVOT TABLES B5 - CHOOSE ( WEEKDAY (B5,2),0,1,2,3,4,5,6) This formula uses WEEKDAY to get an index for the day of week, and CHOOSE to fetch a roll back value. =SUMPRODUCT( (WEEKNUM(Table13]) *Table13 ) Since SUMPRODUCT function comfortably handles arrays, we can use this array of week numbers to create a summary as shown below. The above function does not return an error but an array of week numbers from the given dates range. Just enter a date and the formula returns the week number of that date, f. It can be forced to accept range by adding zero to a range i.e. In this video, we use an Excel formula to return the week number of any given date. It is not entirely true that WEEKNUM does not accept a range argument. Since WEEKNUM does not accept a range argument, then we have to create a helper column so that we can be able to use SUMIF Given the below data, Show totals per week. In this article, I will show you 3 ways to simplify your weekly analysis: So, How do you get week numbers in an array given a range of dates?ĭo you need to always use a helper column to convert dates to week numbers before any analysis is done? =WEEKNUM(K11:K110) just returns #VALUE! error. NB: If the return type is omitted, function defaults to week start on Sunday all the way to Saturday =WEEKNUM( serial_number, ) īeautiful as the function is, it has one major weakness, WEEKNUM doesn’t accept a range argument e.g.

This is mostly so when you are in the production section.Įxcel has a beautiful but overlooked function ( WEEKNUM) that returns an integer representing the week in the year (1 to 53).įor anyone not familiar with WEEKNUM function, it contains 2 arguments Serial_number ( the date to return the week number for) and an optional Return_type (an integer that defines when the week starts).

Every organization requires weekly summaries to monitor peak and off-peak periods.

Excel formula for week number