Week Number in Excel

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.

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?

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.

• Step 1: Open the Excel WEEKNUM function in the B2 cell.

• Step 2: Serial number is nothing but your date cell. So select A2 as the cell reference.

• Step 3: As of now, ignore the last argument. By default, it will consider Sunday as the starting day of the week.

• Step 4: Drag the formula to other cells.

• Step 5: 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.

• Step 6: Now lets the change the start of the week to MONDAY.

• Step 7: 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 excel 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.

Recommended Articles

This has been a guide to WEEKNUM in Excel. Here we discuss how to find Week Number in Excel using the WEEKNUM Function along with practical examples and a downloadable excel template. You may learn more about excel from the following articles –

• Weekday in VBA
• DATEDIF Function in Excel
• EDATE Function in Excel
• DATEVALUE in Excel