How To Find Week Numbers in Excel
Using The Function “WEEKNUM

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

Finding the week number of a specific date by hand is a huge pain—so why not let Excel do it for you?

With the WEEKNUM function, you can get the week number of any date.

Seems simple enough, right?!

But there are a couple things you need to be aware of – this function can actually be a little tricky…

We’ll begin with the basics and then dive into the details. Let’s get started!

Kasper Langmann, Co-founder of Spreadsheeto

*This tutorial is for Excel 2019/Microsoft 365 (for Windows). Got a different version? No problem, you can still follow the exact same steps.

Get your FREE exercise file

If you’d like to follow along with the rest of the article—and we’d recommend it—download the free example worksheet below.

Then open it up and let’s learn the WEEKNUM function!

Download the FREE Exercise File

Download exercise file

The basic WEEKNUM function

As I mentioned, the WEEKNUM takes a date and tells you the week number in the year.

For example, January 1st will always be in week #1. (Except in one special case, which we’ll mention below).

And December 31st will always be #52. Dates in the middle will be assigned their own corresponding week numbers.

Let’s take a look at the syntax before we get started:

The syntax of the WEEKNUM function

=WEEKNUM(date, [return_type])

The date argument is the date that you want the week number of.

Whenever possible, the date should be created using the DATE function or with other functions. If the date is entered as text, the function may not work correctly.

return_type is an optional argument that lets you tell Excel how to count weeks. We’ll get into the details of that a bit later.

Open the first sheet in the example workbook, and you’ll see a few dates. Let’s calculate the week number of those dates.

First, click into cell B2. Then type the following formula:

=WEEKNUM(A2)

Hit Enter.

weeknum-function

A quick look at a calendar confirms that March 20, 2017 was indeed in the 12th week of the year.

A very important note

If you tried counting out the weeks on a calendar, you may be scratching your head. That’s because Excel uses some very specific counting conventions.

Here are things to keep in mind:

January 1st is always in week #1. Even if that week isn’t a full week, it gets counted as the first week of the year.

With no second argument, Excel counts weeks that begin on Sunday and end on Saturday. In the next section, we’ll talk about how to change that.

The WEEKNUM return_type argument

So far, everything has been very straightforward. But when we start talking about the return_type argument, it can get a bit confusing.

When we ran the function the first time, we left the argument blank. Which means it defaults to counting weeks that begin on Sunday.

But including an argument in that field can change the answer you get.

Click into cell C2 and type the following formula:

=WEEKNUM(A2, 2)

Hit Enter, and you’ll get a different answer this time.

weeknum-2

Why?

Because of that 2 in the return_type spot.

You can put a variety of numbers here. And they affect what you’ll get in return.

Here’s what you need to know:

  • 1 (or left blank): week begins on Sunday.
  • 2: week begins on Monday.
  • 11: week begins on Monday.
  • 12: week begins on Tuesday.
  • 13: week begins on Wednesday.
  • 14: week begins on Thursday.
  • 15: week begins on Friday.
  • 16: week begins on Saturday.
  • 17: week begins on Sunday.
  • 21: week begins on Monday (European system; the first week containing a Thursday is counted as the first week in the year).

If you can’t remember this entire list (and who can?), start typing the formula and Excel will pop up a tooltip that gives you the available options:

return-types

Play around with the different return types on the example spreadsheet, and see how your week numbers differ!

Kasper Langmann, Co-founder of Spreadsheeto

Pro tip: include days of the week

When you’re working with weeks that start on a day other than Sunday or Monday, it might be helpful to include the day of the week in the cell format.

Right-click a cell or group of cells and select Format Cells. Select Date from the menu on the left, and then choose an option that includes the day of the week:

format-days

Count weeks like a pro

It can take a little while to get the ins and outs of the WEEKNUM function. But once you do, you’ll be getting week numbers with no hesitation at all.

And if you can remember that it’s possible to count weeks starting on any day, you’ll be even better at it. Stop counting weeks on the calendar and start using Excel to do it faster!