How to Get Week Number in Excel: Quick Tutorial (2023)
Microsoft Excel has a wide variety of functions dealing with dates, weeks, months, and years. And one of these functions is the WEEKNUM function. You might not have heard of it before, and that’s alright.
Precisely speaking, it returns the week number for a given date. For example 26th March 2022 falls in which week of the year (From 1st week to 54 weeks)?
Applying the WEEKNUM function, the answer would be somewhere between 1 to 54 🤔
Interesting? Let’s learn more about the WEEKNUM function of Excel in the guide below. Download our free sample workbook here to tag along with the guide.
Table of Contents
Get the week number with the WEEKNUM function
The WEEKNUM function operates in a very simple manner. It has two arguments in total, one of which is optional.
So, were you able to figure out the week for 26th March 2022 yet? No?
Chill – Excel will do it for you🥂
- Begin writing the WEEKNUM function as follows:
= WEEKNUM (
The first argument of the WEEKNUM function is serial_number.
Excel treats dates as serial numbers, where:
- Serial number 01 is equal to 01 January 1900.
- 02 January 1900 stands equal to serial number 2 and so on.
So 26th March 2022 equates to which serial number 📆
To know this, we must apply the DATE function as follows:
= DATE (2022,03,26)
This returns us the serial number for this date.
And that’s 44646.
If the result of your DATE Function is not a serial number like 44646 but a date like 3/26/2022, it’s in the wrong format.
To fix this, select that Date > Go to Home > Change the format to General.
Great! Back to find the week number for the given date 💪
- Write the first argument (serial_num) of the WEEKNUM function as follows:
= WEEKNUM (44646
The second argument (return_type) is optional.
- Enter a comma to see a list of options for the return_type argument as below:
What does it mean by 1 – Sunday, 2 – Monday, and so on in the image above?
1 means Excel would return the week number assuming all weeks start from Sunday. Under 2, all weeks would start from Monday, and so on 💁♀️
Here are the different return types (and their meaning) that you can use in the WEEKNUM function:
- 1 (or left blank): The week begins on Sunday.
- 2: The week begins on Monday.
- 11: The week begins on Monday.
- 12: The week begins on Tuesday.
- 13: The week begins on Wednesday.
- 14: The week begins on Thursday.
- 15: The week begins on Friday.
- 16: The week begins on Saturday.
- 17: The week begins on Sunday.
- 21: The week begins on Monday (Under the European week numbering system where the first week of the year containing a Thursday is counted as the first week in the year).
That’s all folks! Play around with different return types to see how your week number changes as the starting day of the week changes 🚴♀️
We are for now going with the return_type 2 (This means that the week begins on Monday).
- Write the return_type argument as 2.
= WEEKNUM (44646, 2)
Note that the return_type argument is optional. If omitted Excel assumes it to be equal to 1 or 17. And the week thus starts from Sunday.
- Hit “Enter”.
And there we have it. The date 26th March 2022 falls in the 13th week of the year 2022 🔢
WEEKNUM formula examples
We have now seen the basic application of the WEEKNUM function in Excel. But that’s not it.
Let’s see some more practical examples of how you can apply the WEEKNUM function in Excel 📪
Example No. 1
Here’s the list of a company’s employees along with their date of leaving the company.
Each of these employees who worked with the Company for more than 36 weeks will get fringe benefits 💰
But how do we find those employees? Here’s how.
- Write the WEEKNUM function as follows.
= WEEKNUM (B2,
We want to find the number of weeks each employee has served since the start of the year. For that, we must find the week containing the date when each employee left.
So we have written the leaving date of each employee as the first argument of the WEEKNUM function.
- As the second argument of the WEEKNUM function, write the return_type 2.
= WEEKNUM (B2, 2)
Assuming that the official week of the Company starts from Monday, we have set the return_type to 2 (Monday) 🙈
- Press “Enter” to know the week number when the employee left.
- Drag and drop the same to the whole list.
And it was only that simple to get find when each employee left 🤩
So how many employees qualify for the fringe benefits? Let’s check out here:
- Write the COUNTIF function as follows:
The COUNTIF function counts the number of employees that served the company for more than 36 weeks.
- Press Enter.
Only one employee gets fringe benefits 🥇 We found that super easy, and what about you?
Example No. 2:
Here comes another example of how you may use the WEEKNUM function in Excel.
Below, we have a list of sales made by a Company on different dates.
We want to sum the sales made by the Company in the last week of the year.
The week of the Company here begins on Tuesday 📅
- For that, let’s write the WEEKNUM function as follows:
= WEEKNUM (A2,
As the first argument of the WEEKNUM function, we have added the date for which the week must be found.
- As the second argument, write in the return_type.
The week of the Company starts on Tuesday. Let’s find the return_type argument for Tuesday from the drop-down list here.
The relevant return_type argument here is 12 – write that in ✍
= WEEKNUM (A2, 12)
- Press Enter and drag down the same to the whole list.
Good enough! We know which sale was made in which week. Time to use the SUMIF function to sum the same.
- Write the SUMIF function as follows:
= SUMIF (B2:B5, “>=52”)
This tells Excel to sum up the sales in Column B that fall in Week 52 or later (the last week of the year).
- Press Enter to get the results as follows:
Easy peasy 🍋
That’s it – Now what?
And that’s all about the WEEKNUM function. This function is unique, interesting, and super useful.
In the guide above, we have seen how to use the WEEKNUM function to find the week number for any given date. Not just that – we’ve also come across multiple examples of the WEEKNUM function ✌
The WEEKNUM function can be of great help in situations when you’re short of time. And just like the WEEKNUM function, Excel has many more useful functions.
Some of my favorite Excel functions include the VLOOKUP, SUMIF, and IF functions.
Enroll in my 30-minute free email course here to learn these (and many more) functions of Excel.
Enjoyed the guide above? If that’s a yes, then we bet you’d love to know more about other Date and Time functions of Excel. Learn them here.