Excel WEEKNUM Function – How To Use

The WEEKNUM function is categorized as a DATE/TIME function in Excel. It gives you the week number of the supplied date, which is a value between 1 and 52 weeks.

The function is useful while building a capital budgeting model because it helps an analyst calculate the number of weeks required to complete a project, which can then be used to calculate the number of working days required for the project.

Syntax

The syntax of the WEEKNUM function is as follows:

Arguments:

'serial_number' – This is a required argument where you'll need to supply the serial number representing the date you want to obtain the week number for. You could either enter a date value directly or reference a cell containing a valid Excel date.

'return_type' – This is an optional argument where you may enter a value based on which the first week of the year is counted.

Important Characteristics of the WEEKNUM function

• The value supplied in the serial_number argument must be a valid Excel date.
• If the return_type argument isn't specified, it's taken as 1 by default.
• If the date you supply to the serial_number argument is outside Excel's date base, the function will return a #NUM! error.
• If the code supplied in the return_type argument is any value except the ones mentioned in the table, the function returns a #NUM! error.
• The function is available in MS Excel 2007 and above.

Examples

Let's try to see some examples of the WEEKNUM function.

Example 1 – Plain Vanilla Formula for the WEEKNUM Function

For a moment, let's pin the return_type argument aside and focus on how the WEEKNUM function works in its most basic form. In our example, we'll use a date in January so it's easy to manually calculate weeks and verify the output. Here's the formula you'll need to obtain a date's week number:

By default, the return_type argument is taken as 1, which means the week starts on Sundays. Every Sunday between January 1 and the date supplied in the serial_number argument, adds 1 to the count. There were two Sundays between January 1 and January 15 (i.e., January 7 and 14). The count starts as week 1 on January 1, and the two subsequent Sundays add 2 to the week count which means the final output is 3.

So far so good. Let's bring the return_type argument into the mix now.

Example 2 – WEEKNUM Function with 'return_type'

All codes except 21 work similarly to what we discussed for the default (code 1) in the previous example. Each time a certain day occurs (based on the code used) between January 1 and the supplied date, Excel adds 1 to the week count. For instance, if you use code 11, the week count will be increased by one each time a Monday occurs between January 1 and the supplied date.

All codes from 1 to 17 work pretty straightforward, and code 21 isn't all that different either. Let's use it as an example to see what it does. Here's the formula we'll use:

There are two things that are different here. First, the first week starts on Thursday. Second, after the first Thursday, every Monday adds 1 to the week count.

This time, we've used January 15, 2000. Notice how the output changes to 2 from 3 that the function returned in the previous example.

Reason?

The first week doesn't start until January 6 (Thursday). After which, there's one Monday (January 9) adding 1 to the count, which gives the final output as 2.

Sneaky, right? But not at all complex once you get the hang of it.

Example 3 – Sum by Week Number

Okay, let's roll up those sleeves and dive a little deeper. Say you've got sales made by employees entered into an Excel sheet, along with the date of sale. Right now, the Excel sheet doesn't tell you much about the business's weekly sales performance. You want to convert this data such that you can look at the weekly performance of the business for the month of January.

To do this, you'll need to use a combination of the WEEKNUM function and the SUMIFS function. Start by preparing a helper column using the WEEKNUM function using the following formula:

Once you've done that, list the week numbers on the sheet, and next to/below the listed week numbers, apply the following formula:

The SUMIFS function compares the second argument (criteria) with the third argument (the week number) and sums all cells in the range B2:B13 for which there's a match. In effect, when you apply the formula under week number 1, it sums sales figures that occurred in week 1.

WEEKNUM vs ISOWEEKNUM Function

The difference between these two functions is rather marginal. The WEEKNUM function has a broader scope than the ISOWEEKNUM function. In fact, the WEEKNUM function can do everything that the ISOWEEKNUM function can, plus a little more.

The ISOWEEKNUM function, by default, follows the ISO standards. This means, when you use the ISOWEEKNUM function, it will default to behaving like how the WEEKNUM function would when the return_type argument is set to 21 (refer to example 2).

While the WEEKNUM function also allows you to configure the first day of the week, the ISOWEEKNUM function does not and is therefore limited in scope as compared to the WEEKNUM function.

That's all you need to know about this nifty Excel function. It's an easy-to-use function that can come in handy for plenty of tasks and also work as a tool for extracting information that you could feed into other formulas. While you get comfortable with these formulas, we'll pull out another Excel trick from our hat and give you another Excel function to master. Meanwhile, keep crunching those numbers.