Week Number in Excel (WEEKNUM)

Week Number (WEEKNUM) is a date function in excel which gives us the week number for the given date in a date, it has two arguments in it one which takes a date as an argument which can be a date or a serial number and the other argument is optional which represents the day on which the week begins, the second argument is optional however the first argument is mandatory and the method to use this weeknum function is as follows =Weeknum(Serial Number, Return Type), if the return type is not provided it is considered one by default which Sunday.

You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: Week Number in Excel (wallstreetmojo.com)

Syntax

Serial Number: This is the date, and based on this date, we are trying to find the week number of the date. Excel treats DATE as a serial number.
Return Type: Here, we need to mention the start of the weekday.

We have 10 different options to supply the start of the week. Since this is an optional argument, we need not mandatorily supply the argument. If we leave this argument blank by default, the value will be 1, i.e., the start of the day of the week is Sunday.

The start of the year, Jan 01^st, will always be the first week of the year, and Dec 31^st will always be the last week of the year, i.e., 54^th Week.

How to Use the Week Number (WEEKNUM) Function in Excel?

You can download this WEEKNUM Function Excel Template here – WEEKNUM Function Excel Template

Example #1 – Finding Week Number in Excel

Assume below are the dates you have, and you need to find the week number in excel for each date in that particular year.

Open the Excel WEEKNUM function in the B2 cell.
Serial number is nothing but your date cell. So select A2 as the cell referenceCell Reference Cell reference in excel is referring the other cells to a cell to use its values or properties. For instance, if we have data in cell A2 and want to use that in cell A1, use =A2 in cell A1, and this will copy the A2 value in A1.read more.
As of now, ignore the last argument. By default, it will consider Sunday as the starting day of the week.
Drag the formula to other cells.
We got week numbers for respective dates. But take a look at the cells A2 & A3. Both are 30^th Nov but off a different year. Since excel considers the start of the week is from Sunday, it will vary year to year.
Now lets the change the start of the week to MONDAY.
You can mention the argument as 2. Close the formula.

The result is shown below:

Apply to other cells.

We don’t see any differences here. Try changing the starting day of the week to a different date and find the differences.

Example #2 – Add the Word WEEK to the Week Number in Excel

We have seen how to return the week number of any supplied date. How cool it will be to add the word Week before the week number like the below image.

This is where the advanced formatting technique in excelFormatting Technique In Excel Formatting is a useful feature in Excel that allows you to change the appearance of the data in a worksheet. Formatting can be done in a variety of ways. For example, we can use the styles and format tab on the home tab to change the font of a cell or a table.read more comes into the picture. Follow the below steps to apply the above formatting.

Step 1: Select all the result cells first.

Step 2: Right-click on the selected cells and select Format Cells, or you can directly press the shortcut key Ctrl + 1.

Step 3: Now, we will see below format dialogue box. Go to Custom First.

Step 4: Now enter the format code “Week” # in the Type: area.

Step 5: Press, OK. We will have results ready.

Instead of changing the formatting of the cells, we can also combine the word Week to the result cells. Refer below the image to apply the same.

Example #3 – Calculate Number of Weeks in excel between Two Dates

We have learned the technique of finding the week number of the supplied technique in Excel. How do you tell how many weeks are between two dates?

Assume you have stated the project on 15^th Jan 2018, and you have completed the project on 30^th April 2018. How do you tell how many weeks you had taken to complete the project?

Step 1: Firstly, find the difference between the two dates.

Step 2: Now divide the difference by 7. We have 7 days in a week.

Step 3: Now press enters to complete the formula. We have a result like this.

Step 4: Change the format to general to have accurate results.

Example #4 – Calculate the Number of Weeks in Fraction using Excel

Now let’s consider one more example to have a clear idea about finding the number of the week between two dates in excel.

You are going on a business trip from 15^th Feb 2018, and after all the duties completion, you are coming back on 30^th Jun 2018. Your business head decided to pay you the money on a weekly basis.

Now you are required to tell for how many weeks you are actually going on a business trip. Let’s apply the same formula we have applied in the previous example.

Find the difference between two dates.

Now divide the difference by 7. We have 7 days in a week.

We have a result like this.

Let’s change the format of the resulting cell to fractions.

So we have no., of weeks infractions now.

Things to Remember

By default start of the week in excel is SUNDAY.
If you want to start with a different week, then you need to supply the return type correctly.
If the date is not in the correct format, then we will get #VALUE! Error.

19th/20th September 2020	Saturday - Sunday	9 am IST to 5 pm IST
26th/27th September 2020	Saturday - Sunday	9 am IST to 5 pm IST

Week Number in Excel