WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Week Number in Excel

Week Number in Excel

By Jeevan A YJeevan A Y | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

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 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 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 a 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
View Course

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 the 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 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.

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 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
104 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download WEEKNUM Function Excel Template

Special Offer - All in One Excel VBA Bundle (35 Courses with Projects) View More