WallStreetMojo

WallStreetMojo

Home » Excel, VBA & Power BI » Excel Tutorials » Week Number in Excel

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.

WEEKNUM Function in Excel

Syntax

WEEKNUM Formula in Excel

  • 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 to mandatorily supply the argument, if we leave this argument blank by default value will be 1 i.e. start of the day of the week is Sunday.

Start of the year Jan 01st will always be the first week of the year and Dec 31st will always be the last week of the year i.e. 54th 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.

Week Number Example 1

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

Week Number Example 1-1

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

Week Number Example 1-2

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

Week Number Example 1-3

  • Step 4: Drag the formula to other cells.

Week Number Example 1-4

  • Step 5: We got week numbers for respective dates. But take look at the cells A2 & A3. Both are 30th Nov but off a different year. Since excel considers the start of the week is from Sunday, it will vary year to year.

Week Number Example 1-9

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

Week Number Example 1-5

  • Step 7: you can mention the argument as 2. Close the formula.

Week Number Example 1-6

The result is shown below:

Week Number Example 1-7

Apply to other cells.

Week Number Example 1-8

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.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion

WEEKNUM Example 2

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.

WEEKNUM Example 2

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

WEEKNUM Example 2-1.1

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

WEEKNUM Example 2-1

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

WEEKNUM Example 2-2

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

WEEKNUM Example 2-3

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.

WEEKNUM Example 2-4

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 15th Jan 2018 and you have completed the project on 30th April 2018. How do you tell how many weeks you had taken to complete the project?

WEEKNUM Example 3

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

WEEKNUM Example 3-1

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

WEEKNUM Example 3-2

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

WEEKNUM Example 3-3

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

WEEKNUM Example 3-4

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 15th Feb 2018 and after all the duties completion you are coming back on 30th Jun 2018. Your business head decided to pay you the money on a weekly basis.

WEEKNUM Example 4

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

Find the difference between two dates.

WEEKNUM Example 4-1

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

WEEKNUM Example 4-2

We have a result like this.

WEEKNUM Example 4-3

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

WEEKNUM Example 4-4

So we have no., of weeks infractions now.

WEEKNUM Example 4-5

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 downloadable excel template. You may learn more about excel from the following articles –

104 Shares
Share
Tweet
Share
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>