How to Use WEEKNUM Function in Excel (2 Ways)

In Microsoft Excel, the WEEKNUM function is used to count the week number of a certain year. You can also call this function an Excel TIME and DATE function. In this article, we’ll get to learn how we can use this WEEKNUM function effectively in Excel with appropriate illustrations.

How to Use WEEKNUM Function in Excel

The above screenshot is an overview of the article, representing a few applications of the WEEKNUM function in Excel.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Introduction to the WEEKNUM Function in Excel

Function Objective

The WEEKNUM function is used to calculate the week number of a date

Syntax

=WEEKNUM(serial_number, [returns_type])

Arguments Explanation

Arguments Required/Optional Explanation
serial_number Required Value from which to calculate the week number
returns_type Optional From which day the week begins

The WEEKNUM Function can be used two different [returns_type] ways:

Way 1: Week 1 specifies the week which contains january 1st;

Way 2: Week 1 is the week that includes the first Thursday of the year.

Returns_type Week begins on System
1 or omitted Sunday 1
2 Monday 1
11 Monday 1
12 Tuesday 1
13 Wednesday 1
14 Thursday 1
15 Friday 1
16 Saturday 1
17 Sunday 1
21 Monday 2

2 Suitable Ways to Use YEARFRAC Function in Excel

1. Use the WEEKNUM Function to Calculate the Week Number

Let’s say, we have a dataset where some persons Names and their Dates of Birth are given in Column B and Column C respectively. Now, we will calculate the week number of the Date of Birth of the corresponding year.

Use the WEEKNUM Function to Calculate the Week NumberLet’s say, first we calculate the week number of Dalton’s Birthdate. For this, you have to follow the instructions that are given below:

Step 1:

  • First, select cell D5 and type the WEEKNUM Function in the Formula Bar. The formula is

=WEEKNUM(C5)

  • Where C5 is the Date of Birth value

Use the WEEKNUM Function to Calculate the Week Number

Step 2:

  • Now press Enter on your keyboard and you will get 8 in cell D5 as the Week of Dalton’s Birthday. 

Use the WEEKNUM Function to Calculate the Week Number

Step 3:

  • After that, place your Cursor on the Bottom-Left on Cell D5 and press the Left-Click on your Mouse and drag it downward.
  • Finally, you will be able to count the other person’s Birthdate’s Week of the corresponding year. The week number of all persons is given in the screenshot.

Use the WEEKNUM Function to Calculate the Week Number

Read More: How to Use the DATEDIF Function in Excel


Similar Readings


2. Apply the WEEKNUM Function and DATE Function in Excel

After counting the week number of dates of the corresponding year, we will now count the first date and last date of an arbitrary week by using the combination of WEEKNUM and Date function. To count the first date and last date of an arbitrary week, you have to follow the directions. Let’s say, we will count the first Day and last Day of the week number 5 and year number 2005 from our dataset.

Step 1:

  • First, select the cell D5 and then type the formula in the Formula Bar. the formula will be:

=DATE(B5,1,-2)-WEEKDAY(DATE(B5,1,3))+C5*7

Apply the WEEKNUM Function and DATE Function in Excel

  • After that press Enter on your Keyboard

 DATE

  • After pressing Enter, you will get Monday, January 31, 2005, as the first date of week number

DATE

  • Now, you will get the other date of the corresponding week

Insert DATE

Step 2:

  • Similarly, we will find the last date of week number 5 and year number 2005. For the end date, the formula will be:

=DATE(B5,1,-2)-WEEKDAY(DATE(B5,1,3))+C5*7+6

 insert DATE

  • After that, press Enter and you will get Sunday, February 6, 2005, as the last date of the week. In the same way, you will get the date of another week that has been given in the screenshot.

another procedure

Read More: How to Use Excel EDATE Function


Things to Remember

👉 WEEKNUM Function is used to calculate the week number of a Date of that year.

👉 #VALUE error – happens when

  • Non-numeric.
  • Data is not a valid date.

👉 #NUM error – happens when

  • Function does not permit the value.
  • Data is out of range though numeric.

Conclusion

I hope all of the suitable methods mentioned above to use the WEEKNUM function will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to comment if you have any questions or queries.


Related Articles

Md. Abdur Rahim Rasel

Md. Abdur Rahim Rasel

Hi! I'm Md. Abdur Rahim Rasel. Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. I have completed my graduation in Naval Architecture and Marine Engineering(NAME) from Bangladesh University of Engineering and Technology(BUET). I have a passion for learning new things with my knowledge through perseverance and hard work.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo