Calculating week number in Excel (WEEKNUM function)

Excel WEEKNUM function - syntax

The WEEKNUM function is used in Excel to return the week number of a specific date in the year (a number between 1 and 54). It has two arguments, the 1^st is required and the 2^nd is optional:

• Serial_number - any date within the week whose number you are trying to find. This can be a reference to a cell containing the date, a date entered by using the DATE function or returned by some other formula.
• Return_type (optional) - a number that determines on which day the week begins. If omitted, the default type 1 is used (the week beginning on Sunday).

Here is a complete list of the return_type values supported in WEEKNUM formulas.

In the WEEKNUM function, two different week numbering systems are used:

• System 1. The week containing January 1 is considered the 1^st week of the year and is numbered week 1. In this system, the week traditionally starts on Sunday.
• System 2. This is the ISO week date system that is part of the ISO 8601 date and time standard. In this system, the week starts on Monday and the week containing the first Thursday of the year is considered week 1. It is commonly known as the European week numbering system and it is used mainly in government and business for fiscal years and timekeeping.

All of the return types listed above apply to System 1, except for return type 21 that is used in System 2.

Excel WEEKNUM formulas to convert date to week number (from 1 to 54)

The following screenshot demonstrates how you can get week numbers from dates with the simplest =WEEKNUM(A2) formula:

In the above formula, the return_type argument is omitted, which means that the default type 1 is used - the week beginning on Sunday.

If you'd rather begin with some other day of the week, say Monday, then use 2 in the second argument:

=WEEKNUM(A2, 2)

Instead of referring to a cell, you can specify the date directly in the formula by using the DATE(year, month, day) function, for example:

=WEEKNUM(DATE(2015,4,15), 2)

The above formula returns 16, which is the number of the week containing April 15, 2015, with a week beginning on Monday.

In real-life scenarios, the Excel WEEKNUM function is rarely used on its own. Most often you would use it in combination with other functions to perform various calculations based on the week number, as demonstrated in further examples.

How to convert week number to date in Excel

As you have just seen, it's no big deal to turn a date into a week number using the Excel WEEKNUM function. But what if you are looking for the opposite, i.e. converting a week number to a date? Alas, there is no Excel function that could do this straight away. So, we will have to construct our own formulas.

Supposing you have a year in cell A2 and a week number in B2, and now you want to calculate the Start and End dates in this week.

The formula to return the Start date of the week is as follows:

=DATE(A2, 1, -2) - WEEKDAY(DATE(A2, 1, 3)) + B2 * 7

Where A2 is the year and B2 is the week number.

Please note that the formula returns the date as a serial number, and to have it displayed as a date, you need to format the cell accordingly. You can find the detailed instructions in Changing date format in Excel. And here is the result returned by the formula:

Of course, the formula to convert a week number to a date is not trivial, and it may take a while to get your head round the logic. Anyway, I will do my best to provide meaningful explanation for those who are curious to get down to the bottom.

As you see, our formula consists of 2 parts:

• DATE(A2, 1, -2) - WEEKDAY(DATE(A2, 1, 3)) - calculates the date of the last Monday in the previous year.
• B2 * 7 - adds the number of weeks multiplied by 7 (the number of days in a week) to get the Monday (start date) of the week in question.

In the ISO week numbering system, week 1 is the week containing the first Thursday of the year. Consequently, the first Monday is always between December 29 and January 4. So, to find that date, we have to find the Monday immediately before January 5.

In Microsoft Excel, you can extract a day of week from a date by using the WEEKDAY function. And you can use the following generic formula to get Monday immediately before any given date:

If our ultimate goal were to find Monday immediately before the 5^th of January of the year in A2, we could use the following DATE functions:

=DATE(A2,1,5) - WEEKDAY(DATE(A2,1,3))

But what we actually need is not the first Monday of this year, but rather the last Monday of the previous year. So, you have to subtract 7 days from January 5 and hence you get -2 in the first DATE function:

=DATE(A2,1,-2) - WEEKDAY(DATE(A2,1,3))

Compared to the tricky formula you have just learned, calculating the End date of the week is a piece of cake :) To get Sunday of the week in question, you simply add 6 days to the Start date, i.e. =D2+6

Alternatively, you could add 6 directly in the formula:

=DATE(A2, 1, -2) - WEEKDAY(DATE(A2, 1, 3)) + B2 * 7 + 6

To make sure the formulas always deliver the right dates, please have a look at the following screenshot. The Start Date and End Date formulas discussed above are copied across column D and E, respectively:

How to get month from week number

To get a month corresponding to the week number, you find the first day in a given week as explained in this example, and then wrap that formula in the Excel MONTH function like this:

=MONTH(DATE(A2, 1, -2) - WEEKDAY(DATE(A2, 1, 3)) + B2 * 7)

How to get a week number in a month (from 1 to 6)

If your business logic requires converting a specific date to the week number within the corresponding month, you can use the combination of WEEKNUM, DATE and MONTH functions:

Assuming that cell A2 contains the original date, use the following formula for a week beginning on Monday (notice 21 in WEEKNUM's return_type argument):

=WEEKNUM($A2,21)-WEEKNUM(DATE(YEAR($A2), MONTH($A2),1),21)+1

For a week beginning on Sunday, omit the return_type argument:

=WEEKNUM($A2)-WEEKNUM(DATE(YEAR($A2), MONTH($A2),1))+1

How to sum values and find average by the week number

Now that you know how to convert a date to a week number in Excel, let's see how you can use week numbers in other calculations.

Suppose, you have some monthly sales figures and you want to know the total for each week.

To begin with, let's find out a week number corresponding to each sale. If your dates are in column A and sales in column B, copy the =WEEKNUM(A2) formula across column C beginning in cell C2.

And then, make a list of week numbers in some other column (say, in column E) and calculate the sales for each week using the following SUMIF formula:

=SUMIF($C$2:$C$15, $E2, $B$2:$B$15)

Where E2 is the week number.

In this example, we are working with a list of March sales, so we have week numbers 10 to 14, as demonstrated in the following screenshot:

In a similar manner, you can calculate the sales average for a given week:

=AVERAGEIF($C$2:$C$15, $E2, $B$2:$B$15)

If the helper column with the WEEKNUM formula does not fit well into your data layout, I regret to tell you that there is no simple way to get rid of it because Excel WEEKNUM is one of those functions that doesn't accept range arguments. Therefore, it cannot be used within SUMPRODUCT or any other array formula like the MONTH function in a similar scenario.

How to highlight cells based on the week number

Let's say you have a long list of dates in some column and you want to highlight only those that relate to a given week. All you need is a conditional formatting rule with a WEEKNUM formula similar to this:

=WEEKNUM($A2)=10

As demonstrated in the screenshot below, the rule highlights sales that were made within week 10, which is the first week in March 2015. Since the rule applies to A2:B15, it highlights values in both columns. You can learn more about creating conditional formatting rules in this tutorial: Excel conditional formatting based on another cell value.