WEEKNUM Function

What is the WEEKNUM Function?

The WEEKNUM function in Excel returns the corresponding week number under which the specified date falls.

Dates form the core component of the Excel datasets. Without dates, it wouldn’t be possible to develop meaningful data. For example, dates make it possible to analyze the revenue time series for a company and make assumptions about whether the revenue is growing or falling over time.

However, if the data is distributed across a smaller scale, let’s say, days, it makes it impossible to make accurate assumptions about how the company will do in the near future.

This is especially true for the startups that need to maintain key metrics over time, generally on a weekly or a monthly basis, which is forwarded to the investors for due diligence.

The WEEKNUM is one of those functions that can consolidate data by returning the corresponding week numbers and then grouping them together using the pivot tables.

In this article, we will see the WEEKNUM function and a couple of examples of how the function works.

Understanding the WEEKNUM function

The WEEKNUM is categorized as a Date and Time function that returns the week number in a year for the specified date.

Generally, there are 52 weeks in a single year across which the 365 calendar days are distributed. Thus, seven days from a month are assigned to one week, bringing the total count to four or five weeks in a single month.

If we select any particular date and look to find what week number it falls in, the WEEKNUM function does the trick.

For example, if the date is 1st January 2023 and we use the WEEKNUM function, the function returns the result as 1. Similarly, if the date is 31st December 2023, then the corresponding week number using the function is equal to 53.

WEEKNUM Function Formula

The syntax for the function is

=WEEKNUM(serial_number, [return_type])

where

serial_number - (required) the date for which the week number will be extracted

return_type - (optional) mapping scheme that determines on what weekday the week begins. If the argument is ignored, Excel assumes the value as 1. However, suppose the user intends to use a different value. In that case, the various alternatives are:

WEEKNUM Function Example

You would hardly have any trouble while using the function in general. However, you need to consider what value you would be allocated to the return_type argument.

Even though it is an optional argument, the final result may vary depending on what value you assign to the function.

Example 1

Suppose you have the date as 22nd July 2023 in Excel, as illustrated below:

We will use the formula =WEEKNUM(B3,C3) in cell D3 and drag it down till cell D12, which gives the result:

As you can see, depending on what weekday the week starts on, i.e., Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, or Sunday, the value returned by the WEEKNUM function changes.

Example 2

Earlier, we saw how the return_type argument bought changes in the result returned by the function. However, since it is an optional argument, we won’t focus on it that much.

Instead, let’s take it a step further and understand how you can combine the WEEKNUM function and the pivot tables to generate meaningful data for analysis.

Suppose you have the dates as illustrated below:

To get the respective week numbers, we will use the formula =WEEKNUM(B3,1) in cell D3 and drag it down to cell D14, which gives the result as

But wait, you still cannot make any meaningful insights from the data yet. Next, we will use the pivot tables by selecting the entire data and clicking on Insert > Pivot Table.

Select a new worksheet for generating the pivot table and add the fields in the respective panes as illustrated below:

This will give you the pivot table

Now, the data makes a lot more sense. In the fourth week, a total of $1375 worth of sales were made, while in the fifth week, the sales were just $284.

As a startup founder, if you need to represent the data weekly, the WEEKNUM function and the pivot table can do wonders for you.

WEEKDAY function

The WEEKDAY that falls under the Date and Time function category return the number between one and seven corresponding to different days of the week for a given date value.

The specialty of the function is that it allows the user to assign each value from one to seven implying that each day can start on any particular weekday. This can be done with the help of the additional argument return_type, which lets you assume that your date starts on a particular weekday.

The syntax for the function is:

=WEEKDAY(serial_number, return_type)

where,

• serial_number - (required) the date for which we need to find the day of the week
• return_type - (optional) mapping scheme via which numbers are assigned to different days of the week

By default, Excel assigns the value as 1 if the argument is ignored. The corresponding numbering assigned to the different days of the week is

However, as we said earlier, each day of the week can be assigned a different number from one to seven depending on what argument is used for the return_type argument. The different numbering schemes based on the variable values assigned to the argument are

Each day of the week can be assigned a number from one to seven, as seen above. However, there is an anomaly wherein by using the argument value as 3, the function returns the number between 0 and 6, corresponding to seven days of the week.

Let’s see an example to understand how the function works in action. Suppose we have the data in Excel as illustrated below:

Here, we will use the formula =WEEKDAY(B3,1) in cell C3 and drag it down to cell C8, which gives the result:

Since we had used 1 as the value for the return_type argument, this means that Sunday will be assigned the value as 1, Monday as 2, Tuesday as 3, Wednesday as 4, Thursday as 5, Friday as 6, and finally Saturday as 7.

Thus, 22nd January 2023 falls on Sunday, 24th January 2023 on Tuesday, 26th January 203 on Thursday, and so on. However, this numbering can change depending on your argument for the return_type argument.

Researched and Authored by Akash Bagul | Linkedin

Reviewed and edited by Parul Gupta | LinkedIn

Free Resources

To continue learning and advancing your career, check out these additional helpful WSO resources:

• MATCH Function
• MAX Function
• WEEKDAY Function
• WORKDAY Function
• XIRR Function