How to Get Week Number in Excel: Quick Tutorial (2024)

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.

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🥂

  1. Begin writing the WEEKNUM function as follows:


Writing the excel WEEKNUM function

The first argument of the WEEKNUM function is serial_number.

Pro Tip!

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.

Finding the serial number for a valid excel 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.

Kasper Langmann, Microsoft Office Specialist

Great! Back to find the week number for the given date 💪

  1. Write the first argument (serial_num) of the WEEKNUM function as follows:

= WEEKNUM (44646

Serial_num in the WEEKNUM argument

The second argument (return_type) is optional.

  1. Enter a comma to see a list of options for the return_type argument as below:
The return_type argument options

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 💁‍♀️

Pro Tip!

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

  1. Write the return_type argument as 2.

= WEEKNUM (44646, 2)

Return_type argument set to 2 (iso week date system)

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.

Kasper Langmann, Microsoft Office Specialist
  1. Hit “Enter”.
WEEKNUM returns the week's number

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.

Details of a Company’s employees

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.

  1. Write the WEEKNUM function as follows.


First argument of the worksheet function WEEKNUM

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.

  1. As the second argument of the WEEKNUM function, write the return_type 2.

= WEEKNUM (B2, 2)

Week starts on Monday (2)

Assuming that the official week of the Company starts from Monday, we have set the return_type to 2 (Monday) 🙈

  1. Press “Enter” to know the week number when the employee left.
Excel returns the correct week number
  1. Drag and drop the same to the whole list.
WEEKNUM for all employees

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:

  1. Write the COUNTIF function as follows:

=COUNTIF(C2:C5, “>36”)

Writing the COUNTIF function

The COUNTIF function counts the number of employees that served the company for more than 36 weeks.

  1. Press Enter.
Number of employees

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.

Sales made by Company

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 📅

Kasper Langmann, Microsoft Office Specialist
  1. For that, let’s write the WEEKNUM function as follows:


Sales made by Company

As the first argument of the WEEKNUM function, we have added the date for which the week must be found.

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

Return_type argument drop-down list

The relevant return_type argument here is 12 – write that in ✍

= WEEKNUM (A2, 12)

Week beginning on Tuesday (12)
  1. Press Enter and drag down the same to the whole list.
calculate Week numbers for all dates of sale

Good enough! We know which sale was made in which week. Time to use the SUMIF function to sum the same.

  1. Write the SUMIF function as follows:

= SUMIF (B2:B5, “>=52”)

Writing the SUMIF function

This tells Excel to sum up the sales in Column B that fall in Week 52 or later (the last week of the year).

  1. Press Enter to get the results as follows:
Excel sums all the sales of Week 52 and later

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.

Other resources

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.