WEEKNUM Function

What is the WEEKNUM Function?

The WEEKNUM function is an Excel Date/Time Function that returns the corresponding week number under which the specified date falls. The result returns as value between 1 to 54.

The function is utilized by combining with other functions to perform calculations that are based on weeks. The WEEKNUM function facilitates smoother utilization to work with dates in a week-based format.

Dates form the core component of Excel datasets. Without dates, it wouldn’t be possible to develop meaningful data. For example, dates allow us to analyze a company's revenue time series 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 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 brought 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 returns the number between one and seven corresponding to different days of the week for a given date value.

The function's specialty 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

As seen above, each day of the week can be assigned a number from one to seven. However, there is an anomaly wherein, using the argument value 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 used 1 as the value for the return_type argument, Sunday will be assigned the value 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.

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